Converting from CSV to .xslx and delimiting by semi-colons

Hi,

I am trying to convert all the files in a folder from .csv extension to .xlsx extension. The code provided below accomplishes this:


Workbooks.Open Filename:= _
"E:\Zando\Finance\09.02.2012\Accessories 09.03.2012.csv"
sfilename = "Accessories 09.03.2012.csv"
Windows(sfilename).Activate
Application.ActiveWorkbook.SaveAs Filename:=Left(sfilename, Len(sfilename) - 3) & "xls", FileFormat:=xlWorkbookNormal

However, now the issue is that, although the file is now xlsx it hasn't been delimited. I can't manually delimit each .xlsx document because there are too many.

Does anyone know how I might do this?

Thank you

What do you mean by "it isn't delimited"? Do you mean the csv file contents when opened all appeared in a single column (eg column A)? That's a problem with the way you are opening the csv file if so

Sounds like you need to use the Text to Columns function

Firefly: No the csv file appears as it should if you open it i.e. delimited. But then when I convert it to a .xlsx file using VBA all the data that were in separate columns in the csv file now appear as only one or two columns in the xlsx file

Russell: Yes, I know the Text to Columns function, but in this particular case I can't use it because I want to open the xlsx file ALREADY delimited i.e. don't want to have to delimit it once its already open

Thanks

Open up the VBE and open up the Immediate Window (Ctrl+g) and paste in the following line into it:

Please Login or Register  to view this content.

With the cursor somewhere on this line, hit Enter. This will open the csv. Does it open correctly? Please do not assume that it will do so and therefore fail to follow the steps above.

No, it doesn't open correctly, I didn't notice that before. Is that why it doesn't save the .xlsx file correctly? What can I do to correct that?

Try executing this line and if it works correctly, use this in your code:

Please Login or Register  to view this content.

If it doesn't open correctly with this, can you confirm that the delimiter is actually a comma (and not a semi-colon)?

If so, try this:

Please Login or Register  to view this content.

The first part doesn't work.

Its not a comma, its a semicolon, but I'm assuming I just replaced "comma" with "semiColon". I ran it, but it doesn't run, I don't think it recognises the dataType argument. Its throwing "Named argument not found".

Thanks for your help!

Mike

Can you post the line of code you are trying to execute that is returning "Named Argument Not Found"? The line should be:

Please Login or Register  to view this content.

Note there is no hyphen in Semicolon

Ok, I was using underscore, thanks. It ran this time, but still the same problem, its not delimiting the text.

One last one to try (I've removed the Local argument from above):

Please Login or Register  to view this content.

Nope, still not working.

Thanks for your help though, really appreciate the effort!

Hi, here it is. Each column is properly represented as a column in the csv file, but in the xlsx file they are all compressed into the first two or three columns, separated by semi-colons.

id_catalog simple config
61028 17322 1021 LI451AC77ZQG-61028
61029 17323 1021 LI451AC76ZQH-61029
61035 17329 1021 LI451AC70ZQN-61035

That can be pasted in and then use the text to columns function by fixed width. Your column headers will be sacrificed but should easily be replaced.

Hahaha! Ok I've uploaded it. The forum wouldn't allow me to upload it as a csv, so just changed the file extension to xlsx, but when you get it you can just change it back to csv.
Thanks for your help!

Hahaha! Ok, well I've attached the file now. I've saved it as a .xlsx because it wouldn't allow me to upload a csv, but you can just change the extension once you've downloaded it. Thanks!

Russell: I definitely would do that, but the problem is that when I open the converted excel document, it hasn't even got all the information in one column. Instead of either being one column or all the columns, there are actually about 3 or 4 columns.

Hi and thanks for that. Only mentioned it as many are not aware of it and I got so much use out of it for a project. I was thinking of taking it back a step but it's good to see you have the answer.

Hey snb, thanks so much! That code ran beautifully!

Also to Russell and everyone else who contributed, thanks so much for your help, its really appreciated!