ASCII Again...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I open a CSV, but when I run...

iNumOne = FreeFile
Open "C:\CEFiles\" & cboFileCE For Input As iNumOne
Set Z = CurrentDb
Set RS = Z.OpenRecordset("FromCE")
Do Until EOF(iNumOne)
Input #iNumOne, vStore, vVarZ, vLastInsp, vOneVar, VQues, vTheQues, vAns, _
vComment, vSC, vCD, vCact, vOReason
......
It brings in one long string beginning, and ending with, double-quotes.

What am I doing wrong?

TIA - Bob
 
Bob Barnes said:
Arvin...

Still bringing the entire row in as a string...
Click to expand...

If you are able to use input#, then the data must be CORRECTLY formatted.
That means your data must be separated by comma, and also in most cases the
text data must be CORRECTLY formatted, and that assumes the text parts are
surrounded with a set of quotes.

However, since you data MUST BE correctly formatted to use input, then why
not use the built in import feature of ms-cess?

You might want to paste the first 2, or 3 lines of what the data looks like,
but you going to have ZERO LUCK using input# if you can't get the standard
import to work in the first place....

Somewhere along the way, you taken the wrong curve, since the input#
requites that your data is already in a format that is importable.

So, this begs the question...why not manually import, setup/save the import
spec, and then use transfertext?

I suppose it needs pointing out that if you can't get transfertext to work,
then your input# is NOT going to work.

However, if you can not use the built in import features, then you may very
well have to resort to rolling your own import code, and that being the
case, you will need to use
line input#, and NOT use input#

line input# will pull in a WHOLE line into one variable, and then you must
write your own code to parse out the data....

So, lets clear up why we are not using the built in import, and if not..then
I can't see how the input# is going to work, as it requites the data to
already be in a importable format...
 
Albert...I think this it the proble...

Visual CE makes an "Export" ASCII file like...
050234,"050234",2006-08-24 00:00:00,2006-08-24 00:00:00,"154"....

So using "Input"...I get 050234, (notice Visual CE does NOT put " " around
the first 050234 which is a text...it's treating it like a number.

Then the 2006-08-24 00:00:00,2006-08-24 00:00:00 is brought in with "
preceing it & at the end of the string.

I may have to use line input#...

I'm not sure how to handle this....back to testing an ImportSpec & the Wizard.

Thank you - Bob
 
Here's an entire row...

050234,"050234",2006-08-24 00:00:00,2006-08-24 00:00:00,"154","Is the center
maintaining copies of all hotwork permits issued for the site for a period of
one year?","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
Compliance Manual for a period of one (1) year.","CTOA","Other",2006-08-29
00:00:00,2006-08-30 00:00:00,"hot work permit found",""

If you have a chance...see if you can Import this...either by Wizard or
code...
 
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.....
 
Back
Top