Excel and CSV frustration

anthonydinarda · 2014-10-24T14:16:02.000Z

Currently working on a label automation project at work and ran into a weird lil problem. In the project I have to convert an excel sheet to a csv format and use that as a data filter in our label automation software. While it worked perfectly before, we finally have a final draft of the excel sheet we would be using as a database and the csv isn’t importing into the software correctly. Currently, it is putting some of the column headers into the second line of the excel sheet which is causing the fields to be brought in incorrectly.

My biggest question is what exactly is behind the issue, is this a VBA issue or an excel issue. Any insight as to what software or application may be causing that would be greatly appreciated as it would help me narrow my search for a solution.

Let me know if I need to clarify the issue more.

robt · 2014-10-24T14:19:45.000Z

line separator or a , used in one of the column headers?

open the csv in notepad and check for formatting issues

sqlrage · 2014-10-24T14:20:42.000Z

Here is a script that I did a while back to convert excel files to .csv.

http://community.spiceworks.com/scripts/show/1879-convert-excel-files-to-csvs

Maybe see if it does the same thing.

As for if it is an excel or VBA issue, it is really hard to tell without knowing your process better. How are you currently taking the excel file and creating a csv from it? If you are using VBA what does the export code look like?

anthonydinarda · 2014-10-24T14:21:13.000Z

Hm, didn’t think about opening it up in excel. And yes we are using comma’s to separate the columns. Bout to check that out.

sqlrage · 2014-10-24T14:22:22.000Z

Yeah checking the cells for commas sounds like likely suspect. Good call Rob T.

anthonydinarda · 2014-10-24T14:23:03.000Z

SQLRage:

Here is a script that I did a while back to convert excel files to .csv.

http://community.spiceworks.com/scripts/show/1879-convert-excel-files-to-csvs

Maybe see if it does the same thing.

As for if it is an excel or VBA issue, it is really hard to tell without knowing your process better. How are you currently taking the excel file and creating a csv from it? If you are using VBA what does the export code look like?

Currently I’m just taking the excel sheet and saving it as a csv file. Actually pretty new to the whole database thing and learning as I go so if there are any smarter ways of doing what I am attempting to do please let this rookie know.

marcosjuliomendoza0181 · 2014-10-24T14:26:11.000Z

It’s look like the convertion is made wide fixed and not delimited by commas.

sqlrage · 2014-10-24T14:27:48.000Z

Anthony DiNarda:

SQLRage:

Here is a script that I did a while back to convert excel files to .csv.

http://community.spiceworks.com/scripts/show/1879-convert-excel-files-to-csvs

Maybe see if it does the same thing.

As for if it is an excel or VBA issue, it is really hard to tell without knowing your process better. How are you currently taking the excel file and creating a csv from it? If you are using VBA what does the export code look like?

Currently I’m just taking the excel sheet and saving it as a csv file. Actually pretty new to the whole database thing and learning as I go so if there are any smarter ways of doing what I am attempting to do please let this rookie know.

Essentially, that is all the script I shared does. I like it because I can do it with large quantities of files though.
It is probably not the VBA if that is how you are saving it.

anthonydinarda · 2014-10-24T14:45:30.000Z

Just noticed this. One of the columns in the sheet is for our address which our address has a comma in it. That column was usually at the very end of the excel sheet but is now in the middle which is right where my column headers are getting cut off.

sqlrage · 2014-10-24T14:51:24.000Z

Anthony DiNarda:

Just noticed this. One of the columns in the sheet is for our address which our address has a comma in it. That column was usually at the very end of the excel sheet but is now in the middle which is right where my column headers are getting cut off.

This is one reason I like working with Tab delimited or fixed width when I can. Sometimes you just have to use a csv though…

anthonydinarda · 2014-10-24T15:06:48.000Z

Gonna have to look up tab delimited or fixed width cause I’m bout to punch whoever did csv in the face

allanmacneil5808 · 2014-10-24T16:39:58.000Z

If I remember excel gives you a couple of options for csv. One of them will put quotation marks around the data and escape any special characters in the data such as commas.

anthonydinarda · 2014-10-24T17:13:44.000Z

Allan3894:

If I remember excel gives you a couple of options for csv. One of them will put quotation marks around the data and escape any special characters in the data such as commas.

Just noticed that one of the column headings has quotation marks around it and its around the area that is going wrong. When you look at the excel sheet there is no quotation marks, however, if you open the csv in notepad they are there.