Converting csv file to xls

davidam
davidam used Ask the Experts™
on
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
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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

Commented:
Are you saying you don't want to save a file that did not break the data file into > 1 columns?  just close and move on?

Dave

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Author

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
Of course everything that is included in the quotes was in one cell...that is how csv handled commas in a cell.  
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
well, I'll take a closer look at your script for starters.  send away.

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
and in the meantime, google "use XML to convert from csv to xls"...

Here's one prospect:  http://www.xml-converter.com/

Dave
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
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
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
>> 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.

Author

Commented:
OK, the attached is a csv produced on a client and sent to the server... CLIPrDL.csv
Most Valuable Expert 2012
Top Expert 2014

Commented:
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?

Author

Commented:
Rob,
This is how I am opening the file:
  Set oProtectedFile = xlApp.Workbooks.Open(strProtectedFile,0,false,5, strProtectedFilePassword, "")
Most Valuable Expert 2012
Top Expert 2014
Commented:
OK, try changing that line to this:
Set oProtectedFile = xlApp.Workbooks.Open(strProtectedFile, False, False, , strProtectedFilePassword)

The Open method is documented here (as it applies to the Workbooks object):
http://msdn.microsoft.com/en-us/library/aa191840(v=office.10).aspx
 
and I prefer to use True or False as opposed to zero and one.  Also, for the "format", which is the fourth parameter, I have left it null (blank) so that it uses the default.
 
The setting of 5 refers to "nothing" as the delimiter, possibly suggesting that there is no delimiter, hence all of the data in the one cell.
 
Regards,
 
Rob.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial