Opening CSV in excel from VB

[lonfnico]

Hi again.

I'm trying to open a csv file with a simple macro (which is just gonna
help someone, like a step by step), but my problem is that when I use

- Dim x As Variant
x = Application.GetOpenFilename("Fichiers CSV (*.csv), *.csv")
Workbooks.Open Filename:=x-

(which is msdn code), I will open the CSV as a text file, showing in A1
all my cells separated by semicolumns, instead of opening it as a really
comma-separated values in different cells!

Any idea how to solve that?

Thanks

 

[Guest]

Is the input file truly comma separated? From what you are getting it
appears that perhaps the semicolons are used instead of commas.

For the Workbooks.Open method you can specify what the separator character
is using the Format argument; if my suspicion is correct then this might
resolve the problem:
Workbooks.Open FileName:=x, Format:=4

 

[lonfnico]

Actually, you're totally right. For some reason, in europe, people ofte
use ";" instead of "," in "comma"-SV, no idea why.

But since excel automatically transforms it into a "normal" csv if
doubleclick on it, I thought that, maybe, it would work well wit
OpenFileDialog ^^
Obviously it doesnt!

As for Format:=4, it doesnt work, it might be another format though,
will try.

On the other hand, as I said, I'm brand new to Excel programming. I
there a _very_ good website with all the objects, methods, constants
etc? that are used in VB for excel?

Thanks in advanc

 

[lonfnico]

Format:=4 didnt work, and
Format:=6, Delimiter:=";" doesnt work either.

I have no idea how this damn csv is made, and close to no way to find
out, as its made by some external source.

If I open it with notepad, it doesnt even show the "return" chars, its
just some ascii code (as it seems) that is not taken in charge by
notedpad...

But the ; are still there!

I thought this would take 5 minutes, but finally, im going to use the
day on it

 

[Mikeyhend]

Hi,

You could try this:

Place this in a module in the *.xls that must open the *.csv
I used a button to trigger the macro and separated name and family
name.
If you need more than 2 columns, change array(2,1) to array(?,1) for
the number of columns you need in macro "Text_to_columns".


Sub openCSV()
Workbooks.Open "*:\*\*.csv"
Run "Text_to_columns"
End Sub
Sub Text_to_columns()

Range("A1").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("A1").Select
End Sub

 

[AnExpertNovice]

It is not a CSV file. That is your first mistake. A CSV file is comma
separated and you have a semicolon separated. (Hint Tab Separated is
better, imo.) The comma separated files will load seven fields instead of
six fields because a field contains a comma. To fix this surround each text
field with quotes. That doesn't fix everything but it will help.
Full Name,Doe, John,First Name, John, Last name, Doe

To figure out the proper syntax for loading your specific file follow these
instructions.
Record Macro
Open the file to be imported. (File | Open)
Change the file type to "Text Files (*.prn; *.txt; *.csv)"
Select the file to be loaded.
Click Open
Enable Delimited
Click Next
Select Semicolon as the delimiter and set up any other options as required
Continue through the dialog
Once the file is imported Stop the Macro
Now look at the code that was generated and modify it as necessary.

 

[lonfnico]

It is not a CSV file. That is your first mistake. A CSV file is comma
separated and you have a semicolon separated. (Hint Tab Separated is
better, imo.) The comma separated files will load seven fields instead
of six fields because a field contains a comma. To fix this surround
each text field with quotes. That doesn't fix everything but it will
help.
Full Name,Doe, John,First Name, John, Last name, Doe

As I previously said, I know its not a real CSV. And I can't modify the
source file, as it is not mine - its generated every day by an external
source, I just want to analyse it. But Excel will treat it as a real
CSV, and if I put a comma, without quotes, in the text (like
Name;Adress,postal code;country), it will make 3 fields and not 4 (I
just tested to be sure not to say something wrong). If I just
doubleclick on this *.csv file (which is NOT comma separated, but which
still has the csv extension), Excel will open it properly.

To figure out the proper syntax for loading your specific file follow
these instructions.
Record Macro
Open the file to be imported. (File | Open)
Change the file type to "Text Files (*.prn; *.txt; *.csv)"
Select the file to be loaded.
Click Open
Enable Delimited
Click Next
Select Semicolon as the delimiter and set up any other options as
required
Continue through the dialog
Once the file is imported Stop the Macro
Now look at the code that was generated and modify it as necessary.

I just tried that, sounded like a good idea! But my version of Excel
(2002 sp3) doesn't gives me the opportunity to "Enable Delimited" - it
just opens properly the file, and thats it... and the code generated
looks like this :

Sub MyMacro()
ChDir "D:\Docs\"
Workbooks.Open Filename:= _
"D:\Docs\RAPPORT.csv"
End Sub

As I said, looks like Excel internally treats it, no idea how to change
that...

 

[Randy Harmelink]

How about importing the data into a new worksheet? Then you'll be in
control of how EXCEL interprets the file, even if the file does have
the CSV extension. For example, something like:

Workbooks.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Administrator\My Documents\My
Spreadsheets\Temp\Test.csv" _
, Destination:=Range("A1"))
.AdjustColumnWidth = True
.TextFileParseType = xlDelimited
.TextFileSemicolonDelimiter = True
.Refresh BackgroundQuery:=False
End With