J
Jose
I am using XP Pro SP3 and Office 2007 Enterprise.
I am using a third party retailing WWW page development tool to update
a database of various item information on a WWW page. My boss is a
reseller (at a lower price than the manufacturer of course).
The update takes place via an import function built into their tool
and the import function will take as input a flat file and you can
specify if the field separator is a tab or some other character. I
can't change this part.
I am encouraged to use the tools that are available since I do not
have direct access to the DB (and probably don't want to).
I get a spreadsheet from the suppliers that has at least three
columns: item numbers, descriptions and our cost. I need to add a
lot more information to fill out the other fields on the WWW page and
to so that I manipulate the data in Excel and add more columns so
things will import nicely.
Once I get my spreadsheet looking the way I want, I do a Save As....
to a tab delimited format and am ready to import (usually).
Many times the item description has double quotes in it for inch
indicators - like 4" x 6" and I need that to display on the WWW page
in that format.
If I Save As... the sheet to a tab delimited text file so I can import
it, the descriptions will come out with double quotes around any
column that has a double quote in the description and 4" x 6" becomes
4"" x 6"" (not what I want). I think that is the way it is supposed
to work, but it is not the way I want it to work since I do not want
my entire description to be enclosed in double quotes.
After some reading, my workaround is to change the default XP list
separator character from a comma to a vertical bar (the pipe symbol),
then tell Excel to export the data to a comma separated .CSV file.
Since I changed the Windows list separator character from a comma to a
vertical bar Excel exports the file that way just fine and my field
separator is now a vertical bar instead of a comma, tab or some other
character. I chose the vertical bar since it is unlikely to appear in
any of the descriptions. I can't use a comma as the field separator
since commas could be all over the place in the data.
Then I use a text editor on the Saved As text file and do a global
replace in the file and change all the "| to |, all the |" to | and
all the "" to " and resave it. Then I can tell the database import
program: here is your import text file and the field separator in the
import text file is a vertical bar... and it imports and looks just
fine on the WWW page.
This is some extra work - this text editing and replacing three items
every time for every process. I not only need to perform this process
myself, but I have to document it so somebody else can do it later and
it could be confusing or may break something else - this XP list
separator tampering.
It works fine for me, but I am hoping to eliminate this intermediate
fiddling of the file that occurs after Save As and before Import.
What other ideas are there to help me deal with these double quotes?
I would like to tell Excel to Save As tab delimited, leave the double
quotes alone and when a data column had a double quote in it, leave it
alone and don't put double quotes around the whole thing.
I seem to be missing that option.
Oh yeah - I am XP proficient, but not yet Excel proficient.
I am using a third party retailing WWW page development tool to update
a database of various item information on a WWW page. My boss is a
reseller (at a lower price than the manufacturer of course).
The update takes place via an import function built into their tool
and the import function will take as input a flat file and you can
specify if the field separator is a tab or some other character. I
can't change this part.
I am encouraged to use the tools that are available since I do not
have direct access to the DB (and probably don't want to).
I get a spreadsheet from the suppliers that has at least three
columns: item numbers, descriptions and our cost. I need to add a
lot more information to fill out the other fields on the WWW page and
to so that I manipulate the data in Excel and add more columns so
things will import nicely.
Once I get my spreadsheet looking the way I want, I do a Save As....
to a tab delimited format and am ready to import (usually).
Many times the item description has double quotes in it for inch
indicators - like 4" x 6" and I need that to display on the WWW page
in that format.
If I Save As... the sheet to a tab delimited text file so I can import
it, the descriptions will come out with double quotes around any
column that has a double quote in the description and 4" x 6" becomes
4"" x 6"" (not what I want). I think that is the way it is supposed
to work, but it is not the way I want it to work since I do not want
my entire description to be enclosed in double quotes.
After some reading, my workaround is to change the default XP list
separator character from a comma to a vertical bar (the pipe symbol),
then tell Excel to export the data to a comma separated .CSV file.
Since I changed the Windows list separator character from a comma to a
vertical bar Excel exports the file that way just fine and my field
separator is now a vertical bar instead of a comma, tab or some other
character. I chose the vertical bar since it is unlikely to appear in
any of the descriptions. I can't use a comma as the field separator
since commas could be all over the place in the data.
Then I use a text editor on the Saved As text file and do a global
replace in the file and change all the "| to |, all the |" to | and
all the "" to " and resave it. Then I can tell the database import
program: here is your import text file and the field separator in the
import text file is a vertical bar... and it imports and looks just
fine on the WWW page.
This is some extra work - this text editing and replacing three items
every time for every process. I not only need to perform this process
myself, but I have to document it so somebody else can do it later and
it could be confusing or may break something else - this XP list
separator tampering.
It works fine for me, but I am hoping to eliminate this intermediate
fiddling of the file that occurs after Save As and before Import.
What other ideas are there to help me deal with these double quotes?
I would like to tell Excel to Save As tab delimited, leave the double
quotes alone and when a data column had a double quote in it, leave it
alone and don't put double quotes around the whole thing.
I seem to be missing that option.
Oh yeah - I am XP proficient, but not yet Excel proficient.