import to access from delimited file - file fields have changed.

[girly]

I have been importing a comma-delimited file into an access database for
awhile. Now the file has changed and a new field (actually field now has 2
lines?) has been added and I can't get it to import properly. I have changed
the tables and added a new field. Now when I call up information everything
has shifted one field but not the correct information. Please Help!!!

 

[John Nurick]

Hi Girly,

Normally the standard import routine (the one used by DoCmd.TransferText
or File|Get External Data) can cope with CSV files that have line breaks
in the data, as in this example

1,"Doe","John","This is a one-line comment","Last field"
2,"Roe","Jane","This is a two-line comment
This is the second line of the comment","Last field"
3,"Poe","Edgar Allan","Nevermore","Last Field"

Note that the file with the line break must be delimited (qualified) by
quote marks.

One possible cause of the problem you're seeing is that you are using an
import specification and have not updated the import specification to
reflect the new table structure. If that's not the cause, post back here
with some more information about your data.

 

[girly]

Hi John,
Thanks. I couldn't figure out how to get back to this post for awhile, but
luckily I have found it again!!
More info so maybe you will have a clearer picture as to what I am talking
about;\

old txt file read (partial) "currency",trans code,serial no,"trans
desc",amount,imm avail,one day,two day

new txt file read (partial) "currencey",trans code,serial no,"trans
desc","trans desc 2",amount,imm avail,one day,two day

I added the new field trans desc 2 to the Import table, but when I
import the new txt file and then try to call up information everything has
shifted over and item to be reported in amount is reported in imm avail, etc.

Could you please help?

Thanks!

 

[John Nurick]

Are you using an import specification when you import the data from the
text file? If so, have you changed the specification to include the new
field?

If that's not the answer, it's hard to work out the cause of the problem
without seeing the text file. If the data isn't confidential, would you
like to send it to me attached to an email message? Just remove the
bogus middle name from my address at the head of this message. (If the
file is large, please just send the first 50 or 100 lines.)

 

[girly]

Hi John,
By import specification, do you mean the table that lists all the fields in
the txt file to be imported? If you do, I have inserted a new field for it.
If not, could you please explain.
The info is confidential so I am unable to send the files.
Although the example of the partial file I included in the previous entry in
this thread is exactly the same as the file looks when looking at by notepad.

Thanks!

 

[John Nurick]

Hi John,
By import specification, do you mean the table that lists all the fields in
the txt file to be imported? If you do, I have inserted a new field for it.
If not, could you please explain.

An import specification is a way of specifying how a text file should be
imported. The usual way to create one is by importing or linking the
file manually (File|Get External Data), and clicking the Advanced button
in the text import wizard. Once a specification has been created, you
have to tell Access when to use it: in a manual import, you use the
Advanced button again; and when using the TransferText macro or VBA
statement you use the name of the specification as one of the arguments.
It's usually not necessary to have an import specification when
importing comma-separated files, so if you have never heard of them you
probably don't need one.

The info is confidential so I am unable to send the files.
Although the example of the partial file I included in the previous entry in
this thread is exactly the same as the file looks when looking at by notepad.

The sample you posted was split across two lines by the time it got to
me. I've indicated below where the line break was:

new txt file read (partial) "currencey",trans code,serial no,"trans
<line_break_here>desc","trans desc 2",amount,imm avail,one day,two day

There's no way I can tell at this end whether the line break is part of
the data you see, or whether it was inserted by the newsgroup software.
Moreover, what you posted -
currencey
trans code
serial no
etc. - look more like field names than data in fields. If they're field
names, then they most certainly shouldn't contain any line breaks ...
but in your first message you said that your new field has two lines. So
I'm confused.

Can you post here, or send me, the first three or four records from the
text file. Modify the data to avoid any confidentiality issues (e.g.
change any personal or company names, phone numbers and addresses).
Because what you post here gets wordwrapped, please indicate clearly
every line break either within a field or between records.

 

[girly]

I have copies of files. Not sure which email to send to? is it
(e-mail address removed)?

thanks!

 

[John Nurick]

I have copies of files. Not sure which email to send to? is it
(e-mail address removed)?

Use the reply-to address in this message, but remove the bogus middle
name.

 

[girly]

Hi John,
There is no reply to address in the message.
I have sent an email to you at (e-mail address removed). Is this the
correct address??

thanks!

 

[John Nurick]

No, it's not. I'm not a Microsoft employee and don't have one of their
email addresses.

Here's the address as it appears in the Reply-to header of this message:
(e-mail address removed) . You need to delete the bogus middle
name to get the real one.

 

[John Nurick]

I've received the sample files: thank you.

Unfortunately (or fortunately) I can't find anything wrong with them.
They seem like well-formed CSV files. Each of them imported perfectly to
a new table. The "old" text file contains 16 fields, and the "new" 17
fields, the extra field coming after field 12 in the "old".

I then added a field to the "old" table at the appropriate position (I
had [Field 1] to [Field 16], so made this [Field 12A]. The "new" text
file then imported perfectly into this, with no need for an import
specification or schema.ini.

So - assuming the sample files you sent me are truly representative -
the problem isn't in the text files themselves. Try the following in a
copy of your database:

1) make certain that the field sizes and types in the modified table
really do match those in the "new" text file - and that the fields are
in the right order.

2) if there's a schema.ini file in the same folder as the text file,
rename or remove it.

3) import the "new" text file to the table using File|Get External
Data|Import. In the wizard, select "Delimited", with comma delimiter and
" text qualifier, but don't select any other options.

That worked for me: what happens when you try it?

 

[John Nurick]

The images you emailed show that you haven't been giving me the full
picture, which is a waste of time for both of us.

I created a table in my test database with the same structure as in the
"design view" picture you sent, and imported your "new.txt" sample file
into it (using File|Get External Data Import ... to existing table).

1) The standard import routine doesn't recognise dates stored in the
text file as yyyymmdd (e.g. 20050627), so the TRANS DATE and AS OF DATE
fields were not imported. You must be doing something more than a
straight import to convert these and get them into Access date/time
fields.

2) Your table structure includes fields that are not in the sample data,
namely SerialIsZero and ImportDate, which of course remained empty in
the imported records. You must be doing something more than a straight
import to populate these two fields.

3) All the remaining fields imported correctly.

So how exactly are you importing the data?

I've received the sample files: thank you.

Unfortunately (or fortunately) I can't find anything wrong with them.
They seem like well-formed CSV files. Each of them imported perfectly to
a new table. The "old" text file contains 16 fields, and the "new" 17
fields, the extra field coming after field 12 in the "old".

I then added a field to the "old" table at the appropriate position (I
had [Field 1] to [Field 16], so made this [Field 12A]. The "new" text
file then imported perfectly into this, with no need for an import
specification or schema.ini.

So - assuming the sample files you sent me are truly representative -
the problem isn't in the text files themselves. Try the following in a
copy of your database:

1) make certain that the field sizes and types in the modified table
really do match those in the "new" text file - and that the fields are
in the right order.

2) if there's a schema.ini file in the same folder as the text file,
rename or remove it.

3) import the "new" text file to the table using File|Get External
Data|Import. In the wizard, select "Delimited", with comma delimiter and
" text qualifier, but don't select any other options.

That worked for me: what happens when you try it?

I have copies of files. Not sure which email to send to? is it
(e-mail address removed)?

thanks!