ah..yes, this thread.....
Yes, I was able to import that data...the text file I had was:
Store,@var(1),LastInsp,@var(0),Question,TheQues,Guidance,Answer,Comment,SchCorr,CorrDate,CActions,OtherReason
050234,"050234",2006-08-24 00:00:00,2006-08-24 00:00:00,"154","Is the
service center maintaining copies of all Ashland hotwork permits issued for
the site for a period of one year?","Guidance: If a hotwork permit is
issued for a facility, that facility is required to maintain both the pink
and yellow copies of the permit in their Responsible Care Compliance Manual
for a period of one (1) year.","CTOA","Other",2006-08-29
00:00:00,2006-08-3000:00:00,"hot work permit found",""
I have on purpose put a blank line in the above for ease of reading....
However, if I cut and paste the above into a notepad file, then the above
does import just fine.
So, first, lets assume the file name called
test.csv
fire up ms-access.
Now, go file->get external data
select from the dorop down combo, the
text files (*.txt;*.csv;*.tab;*.asc).
Ok, now browse to our file. (note again, I AM ASSUMING A .CSV FILE
EXTENSION....this is IMPORTANT!!!!).
ok, the text import wizard now comes up. It should make a guess, and default
to
* - delimited - Characters such as comma or tab separate each field
click next...
Now, look carefull on this screen....
For the delimiter, choose the comma (it should be selected for you anyway),
and then choose for text qualifier in the combo box choose the double
Quote -- don't foget this...it is impoart, else your long text with commas
in it will NOT work. So, you MUST set the text quaiflier here in that comb
box to "
Also CLICK THE - first row contians field names box!!!!
now, hit next
we are given the choice to create a new table, or existing...lets put it
into a new table for the time being...
so, ok hit next again,
This screen is IMPORTANT, snce we will now setup the data types for each
collum. You
note how "store" is set to a long data type, lets leave that
@var(1) is a really crappy field name, so, change that field name to somting
better. You can do this for all of the @var() guys.
Further Also MAKE SURE YOU change the date/time types to text types. (they
fail during my test import..so, we will fix them later...just import them as
text...do NOT use date/time here).
You are actually better to hit the advanced tab at this point, as you can
MORE easier edit/change the field names. Make sure you change the date/time
field types to text...
And, don't forget to save this spec, as it can be a lot of work to change
all those things above. (note the save as in advanced button)
So, now run the import, you will have your data in a new table, and you also
will have changed those messy field names to something better. At this
point, we could write a append query to send the data to another existing
table, and also take care of the date/time conversion (if you need them in
date/time format).
the above test data imported without ANY ERRORS!!. That means a table import
error did NOT occur when I did this for the above sample data.
Once you have the data in the temp table, you can really do what you want
with it, and furhter process the formats to how you want.....
