Converting csv file to xls
We are using the following code to convert csv files to xls files
The problem is that sometimes the coversion fails to separate the data into individual cells and instead, puts all of the data into cell A1 of the newly created xls file...can we put something into line 5, the 'SaveAs' line, to ensure that this does not happen? (It should not matter but, note that the new files are being created in Excel 2003, but the csv files may have been created with vba in either 03 or 07)
Thank you
The problem is that sometimes the coversion fails to separate the data into individual cells and instead, puts all of the data into cell A1 of the newly created xls file...can we put something into line 5, the 'SaveAs' line, to ensure that this does not happen? (It should not matter but, note that the new files are being created in Excel 2003, but the csv files may have been created with vba in either 03 or 07)
Thank you
Public Sub Convert_File(strCSVFile)
xlApp.DisplayAlerts = False
Set oCSVToConvert = xlApp.Workbooks.Open(strCSVFile, False, False)
strConvertedName = Left(strCSVFile, Len(strCSVFile) - 4) & ".xls"
oCSVToConvert.SaveAs strConvertedName, xlNormal
oCSVToConvert.Close True
xlApp.DisplayAlerts = True
WScript.Sleep 1000
objFSO.DeleteFile strCSVFile, True
End Sub
Last Comment
ASKER
I need it to save the xls file with the data broken down to separate columns because I use it later on in another procedure.
Ok - can you provide a sample file where this approach is NOT breaking into columns? Your original question doesn't quite get to this type of solution, so let's understand between us exactly what you're looking for - e.g., a fix for something that's not working, versus another approach where we'd need to do the conversion in some steps with VBA.
Cheers,
Dave
Cheers,
Dave
ASKER
OK, I have attached one of the files. -20110513092208.xls
Here is the rest of the story, This process is happening on a server...it receives a csv file via ftp from a client (where the file is made with vba...no vba on server) ...the script on the server converts the csv file to xls so that it can work with the contents of the file. The posted code is part of a larger script that does several other operations. The strange thing is that if I go to Task Manager and shut down the script and then re-enable it...it will work properly once and then start to make this error continuously. I am thinking two things: 1)why is this happening and 2) should I even be using csv? maybe I should be transmitting the data to the server in xml
Here is the rest of the story, This process is happening on a server...it receives a csv file via ftp from a client (where the file is made with vba...no vba on server) ...the script on the server converts the csv file to xls so that it can work with the contents of the file. The posted code is part of a larger script that does several other operations. The strange thing is that if I go to Task Manager and shut down the script and then re-enable it...it will work properly once and then start to make this error continuously. I am thinking two things: 1)why is this happening and 2) should I even be using csv? maybe I should be transmitting the data to the server in xml
Interesting. This one only does a partial conversion on open if I save as CSV first and then open from Excel... due to the quotation marks.
Dave
Dave
ASKER
Of course everything that is included in the quotes was in one cell...that is how csv handled commas in a cell.
I'd like to ask, that since an instance of Excel is initiated as part of your script, a couple coding steps to do a TEXT-TO-COLUMNS then Save as XLS would correct the issue with quotation marks.
Based on what you're sharing, sounds like a different problem, but this particular one would not convert even when your app is "working correctly".
Would you like to try some code that does the conversion via a Text To Columns step? it could be the primary code, or, you could test column B for data and if not, then run the Text to Columns on Column A....
Sounds like a patch, I know, but perhaps worth a try?
Dave
Based on what you're sharing, sounds like a different problem, but this particular one would not convert even when your app is "working correctly".
Would you like to try some code that does the conversion via a Text To Columns step? it could be the primary code, or, you could test column B for data and if not, then run the Text to Columns on Column A....
Sounds like a patch, I know, but perhaps worth a try?
Dave
RIght - I needed to expand the columns to see the result - the quotation marks are not an issue, lol.
But let me know what you think about the other alternative.... Sounds klunky, but if you can't get root cause on the first, you perhaps improve odds or fix the symption with the second approach (e.g., having excel do a TextToColumns conversion on column A...
Dave
But let me know what you think about the other alternative.... Sounds klunky, but if you can't get root cause on the first, you perhaps improve odds or fix the symption with the second approach (e.g., having excel do a TextToColumns conversion on column A...
Dave
ASKER
The fact that it occurs only part of the time suggests to me that there is nothing wrong with the way the scripot is written..it is just some kind of bug. Would not just switching to xml be a better solution? .I am just not sure how to accomplish this...there needs to be some kind of modification of the conversion script and the objects need to be changed somehow but this is beyond my knowledge level at this time.
well, I'll take a closer look at your script for starters. send away.
Dave
Dave
and in the meantime, google "use XML to convert from csv to xls"...
Here's one prospect: http://www.xml-converter.com/
Dave
Here's one prospect: http://www.xml-converter.com/
Dave
Hi, can you send one of the CSV's as well? I think we might be able to remove the quotation marks from the raw CSV before opening in Excel to aid the conversion.
Regards,
Rob.
Regards,
Rob.
ASKER
Ok here is a sample csv file...the thing is Rob, that it works once and then errors even if there are no quotation marks...that is why I think I should switch to transferring the data with xml instead of csv..
-20110513163830.xls
-20110513163830.xls
The attached has an XLS extension....do I change it to CSV....I'll do some testing chucking it through the process twice to see what happens.
Rob.
Rob.
ASKER
Hi guys, sorry, I have a houseful and I am just checking in from time to time...that file was a csv file that came in and what you see is the result of it being converted to xls by the process...if you changed it back to csv, it would be exactly the same as its original form.
>> I have a houseful
Me too....kidlets running around!
>> if you changed it back to csv, it would be exactly the same as its original form
Not necessarily, Excel does some automatic formatting on data when you open a CSV....the original CSV would be better for testing. Sorry to be a pain.
Rob.
Me too....kidlets running around!
>> if you changed it back to csv, it would be exactly the same as its original form
Not necessarily, Excel does some automatic formatting on data when you open a CSV....the original CSV would be better for testing. Sorry to be a pain.
Rob.
ASKER
OK, the attached is a csv produced on a client and sent to the server... CLIPrDL.csv
David, with the snippet you've provided, I cannot fault the conversion of the file. Perhaps it has something to do with how you're opening the file?
ASKER
Rob,
This is how I am opening the file:
Set oProtectedFile = xlApp.Workbooks.Open(strPr
This is how I am opening the file:
Set oProtectedFile = xlApp.Workbooks.Open(strPr
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Dave