Inconsistent CSV export format

[RichardOKeefe]

I'm retrieving data from an Oracle database ino a CSV file so that it can be
manipulated in Excel, saved back to a CSV file and then imported back the
Oracle database. Part of the manipulation is to remove the last couple of
columns which are for display purposes only. The last column left after
deleting the display columns can have null values ie blank cells. My problem
is that when I save the file back to CSV format, rows that end in a blank
column sometimes end with a comma to denote the last blank or null column and
sometimes don't. The ones that are missing the trailing comma don't load
into Oracle properly. They do load into a table becasue Oracle recognises
trailing null columns but I'm loading the last several columns as one to
process them separately It is this process that is failing because rows that
are missing the trailing comma cause a mismatch with the expected number of
columns being loaded back in.

The first row of the CSV file is a set of headers that define the columns in
each row.

Is there some way of getting around this eg cell formatting?

Thanks in advance.

[Lotus123]

I can't think of any way to do a custom format that would display a , if the field was blank...so here are my thoughts:

Select the column, Control-G (brings up Goto), Select Special, Select Blanks. Now every cell without a comma is selected. Type a comma and press Control-Enter. This will place a single comma in every blank cell. Of course, you would have to do this every time you manipulate the data, but you could set it up as a macro.

[Bryan Hessey]

One suggestion, best suited to occasional use, is to fill the next column
(ie, the first column you dont want) with a data that you wont find as real
data (say $$4$$ )

You then have the choice of ignoring the last field in Oracle as you input,
or you can open the file in Notepad and replace ,"$$4$$" (that is replace
comma quote $$4$$ quote ) with nothing. This will give you the correct number
of field separators, but is rather time consuming, especially on a large file
and not a process you would wish to repeat often.

Trying to enter a comma as a cell value will, unfortunately, cause such
commas to be enclosed in quotes when output to a .csv file, this could cause
a problem as the comma then becomes part of your data.

Hopefully there is a better solution.

--

"RichardOKeefe" wrote:

> I'm retrieving data from an Oracle database ino a CSV file so that it can be
> manipulated in Excel, saved back to a CSV file and then imported back the
> Oracle database. Part of the manipulation is to remove the last couple of
> columns which are for display purposes only. The last column left after
> deleting the display columns can have null values ie blank cells. My problem
> is that when I save the file back to CSV format, rows that end in a blank
> column sometimes end with a comma to denote the last blank or null column and
> sometimes don't. The ones that are missing the trailing comma don't load
> into Oracle properly. They do load into a table becasue Oracle recognises
> trailing null columns but I'm loading the last several columns as one to
> process them separately It is this process that is failing because rows that
> are missing the trailing comma cause a mismatch with the expected number of
> columns being loaded back in.
>
> The first row of the CSV file is a set of headers that define the columns in
> each row.
>
> Is there some way of getting around this eg cell formatting?
>
> Thanks in advance.

[exceluserforeman]

Try my CSV exporter at
http://au.geocities.com/excelmarksway

Text Writer Section

workingCSV_01.xls

I am not sure if it will do the job but would be interested to know if it
does!

exceluserforeman


"RichardOKeefe" wrote:

> I'm retrieving data from an Oracle database ino a CSV file so that it can be
> manipulated in Excel, saved back to a CSV file and then imported back the
> Oracle database. Part of the manipulation is to remove the last couple of
> columns which are for display purposes only. The last column left after
> deleting the display columns can have null values ie blank cells. My problem
> is that when I save the file back to CSV format, rows that end in a blank
> column sometimes end with a comma to denote the last blank or null column and
> sometimes don't. The ones that are missing the trailing comma don't load
> into Oracle properly. They do load into a table becasue Oracle recognises
> trailing null columns but I'm loading the last several columns as one to
> process them separately It is this process that is failing because rows that
> are missing the trailing comma cause a mismatch with the expected number of
> columns being loaded back in.
>
> The first row of the CSV file is a set of headers that define the columns in
> each row.
>
> Is there some way of getting around this eg cell formatting?
>
> Thanks in advance.

[RichardOKeefe]

Thanks for the reply. Tried downloading the file but it came back with a
page not found error.

Richard

"exceluserforeman" wrote:

> Try my CSV exporter at
> http://au.geocities.com/excelmarksway
>
> Text Writer Section
>
> workingCSV_01.xls
>
> I am not sure if it will do the job but would be interested to know if it
> does!
>
> exceluserforeman
>
>
> "RichardOKeefe" wrote:
>
> > I'm retrieving data from an Oracle database ino a CSV file so that it can be
> > manipulated in Excel, saved back to a CSV file and then imported back the
> > Oracle database. Part of the manipulation is to remove the last couple of
> > columns which are for display purposes only. The last column left after
> > deleting the display columns can have null values ie blank cells. My problem
> > is that when I save the file back to CSV format, rows that end in a blank
> > column sometimes end with a comma to denote the last blank or null column and
> > sometimes don't. The ones that are missing the trailing comma don't load
> > into Oracle properly. They do load into a table becasue Oracle recognises
> > trailing null columns but I'm loading the last several columns as one to
> > process them separately It is this process that is failing because rows that
> > are missing the trailing comma cause a mismatch with the expected number of
> > columns being loaded back in.
> >
> > The first row of the CSV file is a set of headers that define the columns in
> > each row.
> >
> > Is there some way of getting around this eg cell formatting?
> >
> > Thanks in advance.

[RichardOKeefe]

Thanks for the suggestions. These will suffice as a workaround as the
procedure is only used infrequently. However as it's used to define people's
pay increments it's highly important to the clients that use it.

"RichardOKeefe" wrote:

> I'm retrieving data from an Oracle database ino a CSV file so that it can be
> manipulated in Excel, saved back to a CSV file and then imported back the
> Oracle database. Part of the manipulation is to remove the last couple of
> columns which are for display purposes only. The last column left after
> deleting the display columns can have null values ie blank cells. My problem
> is that when I save the file back to CSV format, rows that end in a blank
> column sometimes end with a comma to denote the last blank or null column and
> sometimes don't. The ones that are missing the trailing comma don't load
> into Oracle properly. They do load into a table becasue Oracle recognises
> trailing null columns but I'm loading the last several columns as one to
> process them separately It is this process that is failing because rows that
> are missing the trailing comma cause a mismatch with the expected number of
> columns being loaded back in.
>
> The first row of the CSV file is a set of headers that define the columns in
> each row.
>
> Is there some way of getting around this eg cell formatting?
>
> Thanks in advance.

[exceluserforeman]

You can use VB to extract the data you want and write the extracted data to a
new text (csv) file. If you only want the extracted data and no changes then
not a problem but if you want to modify the columns then that is another
matter, however if the input data is known then it can bre added whenthe new
file is created.
You be best to hire someone to do the job. I could do it but my rates are
very high.

Look in VB Editor Help for "csv" or "input". All the answers are in VB help.
You could do it yourself.

- -Mark




"RichardOKeefe" wrote:

> I'm retrieving data from an Oracle database ino a CSV file so that it can be
> manipulated in Excel, saved back to a CSV file and then imported back the
> Oracle database. Part of the manipulation is to remove the last couple of
> columns which are for display purposes only. The last column left after
> deleting the display columns can have null values ie blank cells. My problem
> is that when I save the file back to CSV format, rows that end in a blank
> column sometimes end with a comma to denote the last blank or null column and
> sometimes don't. The ones that are missing the trailing comma don't load
> into Oracle properly. They do load into a table becasue Oracle recognises
> trailing null columns but I'm loading the last several columns as one to
> process them separately It is this process that is failing because rows that
> are missing the trailing comma cause a mismatch with the expected number of
> columns being loaded back in.
>
> The first row of the CSV file is a set of headers that define the columns in
> each row.
>
> Is there some way of getting around this eg cell formatting?
>
> Thanks in advance.