If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
CSV dropping last comma for all lines after line 15
Hi there, I hope someone has seen this before and knows the fix! We have a
csv file format that we use to upload data to our custom system. The last 3 columns in the file are optional so they can sometimes be blank. We have noticed that Excel is dropping the last comma on the lines beyond data line 15. So for example, I have a file with a header row and 20 rows of data. Say all these data rows have the last 3 columns blank. If I save the file in CSV and upload it, the system fails the upload since the lines 16 and beyond have a missing comma! So I open the file in notepad to see what's up and I notice that data lines 1 - 15 end with 3 commas ,,, since the columns were blank. But the data lines 16-20 only had 2 commas! The last comma dropped!! It is consistently happening with every file I have that has more than 15 lines! Thanks for your help. KZ |
#2
|
|||
|
|||
CSV dropping last comma for all lines after line 15
Maybe you could just put a formula that evaluates to "" in the last column of
the rows that are empty. ="" Select the range in the last column edit|goto|special|blanks (ok) type ="" and hit ctrl-enter Then try saving the file as a CSV file. KZ wrote: Hi there, I hope someone has seen this before and knows the fix! We have a csv file format that we use to upload data to our custom system. The last 3 columns in the file are optional so they can sometimes be blank. We have noticed that Excel is dropping the last comma on the lines beyond data line 15. So for example, I have a file with a header row and 20 rows of data. Say all these data rows have the last 3 columns blank. If I save the file in CSV and upload it, the system fails the upload since the lines 16 and beyond have a missing comma! So I open the file in notepad to see what's up and I notice that data lines 1 - 15 end with 3 commas ,,, since the columns were blank. But the data lines 16-20 only had 2 commas! The last comma dropped!! It is consistently happening with every file I have that has more than 15 lines! Thanks for your help. KZ -- Dave Peterson |
#3
|
|||
|
|||
CSV dropping last comma for all lines after line 15
Dave,
Thanks so much for the suggestion. It worked for me. I can use that as a manual workaround for now. Ideally, though, I would like to know if this is a bug and if there is a fix if so... Again, thanks for the info! Karine "Dave Peterson" wrote: Maybe you could just put a formula that evaluates to "" in the last column of the rows that are empty. ="" Select the range in the last column edit|goto|special|blanks (ok) type ="" and hit ctrl-enter Then try saving the file as a CSV file. KZ wrote: Hi there, I hope someone has seen this before and knows the fix! We have a csv file format that we use to upload data to our custom system. The last 3 columns in the file are optional so they can sometimes be blank. We have noticed that Excel is dropping the last comma on the lines beyond data line 15. So for example, I have a file with a header row and 20 rows of data. Say all these data rows have the last 3 columns blank. If I save the file in CSV and upload it, the system fails the upload since the lines 16 and beyond have a missing comma! So I open the file in notepad to see what's up and I notice that data lines 1 - 15 end with 3 commas ,,, since the columns were blank. But the data lines 16-20 only had 2 commas! The last comma dropped!! It is consistently happening with every file I have that has more than 15 lines! Thanks for your help. KZ -- Dave Peterson |
#4
|
|||
|
|||
CSV dropping last comma for all lines after line 15
Saved from a previous post:
This might describe the problem of too many commas in CSV files: http://support.microsoft.com/default.aspx?scid=77295 Column Delimiters Missing in Spreadsheet Saved as Text (It actually describes missing delimiter, but if some are "missing", maybe the ones appearing are "extra".) (But a lot of programs (excel included) don't care about those extra columns. Maybe you don't have to care, either???) Maybe you could write your own exporting program that would behave exactly the way you want: Here are three sites that you could steal some code from: Earl Kiosterud's Text Write program: www.smokeylake.com/excel (or directly: http://www.smokeylake.com/excel/text_write_program.htm) Chip Pearson's: http://www.cpearson.com/excel/imptext.htm J.E. McGimpsey's: http://www.mcgimpsey.com/excel/textfiles.html (or maybe you could build your own formula and copy|paste into Notepad.) KZ wrote: Dave, Thanks so much for the suggestion. It worked for me. I can use that as a manual workaround for now. Ideally, though, I would like to know if this is a bug and if there is a fix if so... Again, thanks for the info! Karine "Dave Peterson" wrote: Maybe you could just put a formula that evaluates to "" in the last column of the rows that are empty. ="" Select the range in the last column edit|goto|special|blanks (ok) type ="" and hit ctrl-enter Then try saving the file as a CSV file. KZ wrote: Hi there, I hope someone has seen this before and knows the fix! We have a csv file format that we use to upload data to our custom system. The last 3 columns in the file are optional so they can sometimes be blank. We have noticed that Excel is dropping the last comma on the lines beyond data line 15. So for example, I have a file with a header row and 20 rows of data. Say all these data rows have the last 3 columns blank. If I save the file in CSV and upload it, the system fails the upload since the lines 16 and beyond have a missing comma! So I open the file in notepad to see what's up and I notice that data lines 1 - 15 end with 3 commas ,,, since the columns were blank. But the data lines 16-20 only had 2 commas! The last comma dropped!! It is consistently happening with every file I have that has more than 15 lines! Thanks for your help. KZ -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
CSV dropping last comma for all lines after line 15
This is great info Dave. Thanks for your quick responses! I will forward this
info to my tech team to see if they can re-use. thanks again. "Dave Peterson" wrote: Saved from a previous post: This might describe the problem of too many commas in CSV files: http://support.microsoft.com/default.aspx?scid=77295 Column Delimiters Missing in Spreadsheet Saved as Text (It actually describes missing delimiter, but if some are "missing", maybe the ones appearing are "extra".) (But a lot of programs (excel included) don't care about those extra columns. Maybe you don't have to care, either???) Maybe you could write your own exporting program that would behave exactly the way you want: Here are three sites that you could steal some code from: Earl Kiosterud's Text Write program: www.smokeylake.com/excel (or directly: http://www.smokeylake.com/excel/text_write_program.htm) Chip Pearson's: http://www.cpearson.com/excel/imptext.htm J.E. McGimpsey's: http://www.mcgimpsey.com/excel/textfiles.html (or maybe you could build your own formula and copy|paste into Notepad.) KZ wrote: Dave, Thanks so much for the suggestion. It worked for me. I can use that as a manual workaround for now. Ideally, though, I would like to know if this is a bug and if there is a fix if so... Again, thanks for the info! Karine "Dave Peterson" wrote: Maybe you could just put a formula that evaluates to "" in the last column of the rows that are empty. ="" Select the range in the last column edit|goto|special|blanks (ok) type ="" and hit ctrl-enter Then try saving the file as a CSV file. KZ wrote: Hi there, I hope someone has seen this before and knows the fix! We have a csv file format that we use to upload data to our custom system. The last 3 columns in the file are optional so they can sometimes be blank. We have noticed that Excel is dropping the last comma on the lines beyond data line 15. So for example, I have a file with a header row and 20 rows of data. Say all these data rows have the last 3 columns blank. If I save the file in CSV and upload it, the system fails the upload since the lines 16 and beyond have a missing comma! So I open the file in notepad to see what's up and I notice that data lines 1 - 15 end with 3 commas ,,, since the columns were blank. But the data lines 16-20 only had 2 commas! The last comma dropped!! It is consistently happening with every file I have that has more than 15 lines! Thanks for your help. KZ -- Dave Peterson -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|