Importing a file into Access...

I am importing a .csv file into Access that has 37 fields. My problem is
that sometimes the last field only has data at the end of the column (it
looks like when you import a file into Access, for the last field, it
only checks the top few 'cells' to see if there is any data, if not, the
field is not imported).

How do I 'force' Access to import the field, regardless if there is data
in the top of the field or not? For instance, I might have 1,000 records
that I am importing. If only the last 100 records have data in that
field, then, I lose the data because it is not importing the field.

Thanks,
Grim Reaper

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Use an import spec.

Place a value in the last column of a sample csv.
Start the import manually: File | Get External | Import.
After selecting the CSV file, click the Advanced button to save an import
spec.

When you do the import in future, include the import spec in the
TransferText so Access knows how many columns it must get.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Grim Reaper" <reaper@nospam.com> wrote in message
news:3fcfbc92$0$88383$75868355@news.frii.net...[color=blue]
> I am importing a .csv file into Access that has 37 fields. My problem is
> that sometimes the last field only has data at the end of the column (it
> looks like when you import a file into Access, for the last field, it
> only checks the top few 'cells' to see if there is any data, if not, the
> field is not imported).
>
> How do I 'force' Access to import the field, regardless if there is data
> in the top of the field or not? For instance, I might have 1,000 records
> that I am importing. If only the last 100 records have data in that
> field, then, I lose the data because it is not importing the field.[/color]

I don't quite remember the correct syntx but make sure the check box for the
"top record for field name" option is off. You might want to create a
record that will appear first in the native db sort and that has all the
fiel name discriptions so that when you import the file with the previous
said option on you won't have that many errors. If you cant create that
record, create it manually in a text editor. Hope this helps.

figueroa at ync dot com

Hi Allen,

When I click the Advanced button, all I get is an Advanced Find dialog
box. How does one create and save an import spec? I'm jumping in here
because I've heard of an Import Spec but the help file doesn't mention
it.

Thanks


"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message news:<3fcfef94$0$1757$5a62ac22@freenews.iinet.net. au>...[color=blue]
> Use an import spec.
>
> Place a value in the last column of a sample csv.
> Start the import manually: File | Get External | Import.
> After selecting the CSV file, click the Advanced button to save an import
> spec.
>
> When you do the import in future, include the import spec in the
> TransferText so Access knows how many columns it must get.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Grim Reaper" <reaper@nospam.com> wrote in message
> news:3fcfbc92$0$88383$75868355@news.frii.net...[color=green]
> > I am importing a .csv file into Access that has 37 fields. My problem is
> > that sometimes the last field only has data at the end of the column (it
> > looks like when you import a file into Access, for the last field, it
> > only checks the top few 'cells' to see if there is any data, if not, the
> > field is not imported).
> >
> > How do I 'force' Access to import the field, regardless if there is data
> > in the top of the field or not? For instance, I might have 1,000 records
> > that I am importing. If only the last 100 records have data in that
> > field, then, I lose the data because it is not importing the field.[/color][/color]

Ed,

In case he doesn't get back to you for a while:

When you import text files, you automatically get an Import Text Wizard
dialog box. In the lower left corner is an Advanced button. Click it to
see the Import Spec dialog box. On this screen you can specify lots of
setting for importing (and exporting) text files.



"Ed Marzan" <ducojansen@aol.com> wrote in message
news:cc123933.0312051035.4db566be@posting.google.c om...[color=blue]
> Hi Allen,
>
> When I click the Advanced button, all I get is an Advanced Find dialog
> box. How does one create and save an import spec? I'm jumping in here
> because I've heard of an Import Spec but the help file doesn't mention
> it.
>
> Thanks
>
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message[/color]
news:<3fcfef94$0$1757$5a62ac22@freenews.iinet.net. au>...[color=blue][color=green]
> > Use an import spec.
> >
> > Place a value in the last column of a sample csv.
> > Start the import manually: File | Get External | Import.
> > After selecting the CSV file, click the Advanced button to save an[/color][/color]
import[color=blue][color=green]
> > spec.
> >
> > When you do the import in future, include the import spec in the
> > TransferText so Access knows how many columns it must get.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Grim Reaper" <reaper@nospam.com> wrote in message
> > news:3fcfbc92$0$88383$75868355@news.frii.net...[color=darkred]
> > > I am importing a .csv file into Access that has 37 fields. My problem[/color][/color][/color]
is[color=blue][color=green][color=darkred]
> > > that sometimes the last field only has data at the end of the column[/color][/color][/color]
(it[color=blue][color=green][color=darkred]
> > > looks like when you import a file into Access, for the last field, it
> > > only checks the top few 'cells' to see if there is any data, if not,[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > field is not imported).
> > >
> > > How do I 'force' Access to import the field, regardless if there is[/color][/color][/color]
data[color=blue][color=green][color=darkred]
> > > in the top of the field or not? For instance, I might have 1,000[/color][/color][/color]
records[color=blue][color=green][color=darkred]
> > > that I am importing. If only the last 100 records have data in that
> > > field, then, I lose the data because it is not importing the field.[/color][/color][/color]

Select the file, and open it.
Then choose the Advanced button in the next dialog.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ed Marzan" <ducojansen@aol.com> wrote in message
news:cc123933.0312051035.4db566be@posting.google.c om...[color=blue]
> Hi Allen,
>
> When I click the Advanced button, all I get is an Advanced Find dialog
> box. How does one create and save an import spec? I'm jumping in here
> because I've heard of an Import Spec but the help file doesn't mention
> it.
>
> Thanks
>
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message[/color]
news:<3fcfef94$0$1757$5a62ac22@freenews.iinet.net. au>...[color=blue][color=green]
> > Use an import spec.
> >
> > Place a value in the last column of a sample csv.
> > Start the import manually: File | Get External | Import.
> > After selecting the CSV file, click the Advanced button to save an[/color][/color]
import[color=blue][color=green]
> > spec.
> >
> > When you do the import in future, include the import spec in the
> > TransferText so Access knows how many columns it must get.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Grim Reaper" <reaper@nospam.com> wrote in message
> > news:3fcfbc92$0$88383$75868355@news.frii.net...[color=darkred]
> > > I am importing a .csv file into Access that has 37 fields. My problem[/color][/color][/color]
is[color=blue][color=green][color=darkred]
> > > that sometimes the last field only has data at the end of the column[/color][/color][/color]
(it[color=blue][color=green][color=darkred]
> > > looks like when you import a file into Access, for the last field, it
> > > only checks the top few 'cells' to see if there is any data, if not,[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > field is not imported).
> > >
> > > How do I 'force' Access to import the field, regardless if there is[/color][/color][/color]
data[color=blue][color=green][color=darkred]
> > > in the top of the field or not? For instance, I might have 1,000[/color][/color][/color]
records[color=blue][color=green][color=darkred]
> > > that I am importing. If only the last 100 records have data in that
> > > field, then, I lose the data because it is not importing the field.[/color][/color][/color]

AllenBrowne@SeeSig.Invalid (Allen Browne) wrote in
<3fd0e4a3$0$1729$5a62ac22@freenews.iinet.net.au> :
[color=blue]
>Select the file, and open it.
>Then choose the Advanced button in the next dialog.[/color]

That allows you to make changes to the import spec, but you still
have to save it, which requires giving it a name. Then you can call
the import spec by name when importing with TransferText.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Sorry, I'm importing Excel spreadsheets and there does not appear to
be a way to save the import spec. I guess it only works with text
files. I'll try exporting the Excel to CSV to see if that works. I'm
ussing Acc 97

Thanks for your help.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message news:<3fd0e4a3$0$1729$5a62ac22@freenews.iinet.net. au>...[color=blue]
> Select the file, and open it.
> Then choose the Advanced button in the next dialog.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Ed Marzan" <ducojansen@aol.com> wrote in message
> news:cc123933.0312051035.4db566be@posting.google.c om...[color=green]
> > Hi Allen,
> >
> > When I click the Advanced button, all I get is an Advanced Find dialog
> > box. How does one create and save an import spec? I'm jumping in here
> > because I've heard of an Import Spec but the help file doesn't mention
> > it.
> >
> > Thanks
> >
> >
> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message[/color]
> news:<3fcfef94$0$1757$5a62ac22@freenews.iinet.net. au>...[color=green][color=darkred]
> > > Use an import spec.
> > >
> > > Place a value in the last column of a sample csv.
> > > Start the import manually: File | Get External | Import.
> > > After selecting the CSV file, click the Advanced button to save an[/color][/color]
> import[color=green][color=darkred]
> > > spec.
> > >
> > > When you do the import in future, include the import spec in the
> > > TransferText so Access knows how many columns it must get.
> > >
> > > --
> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > Tips for Access users - http://allenbrowne.com/tips.html
> > > Reply to group, rather than allenbrowne at mvps dot org.
> > >
> > > "Grim Reaper" <reaper@nospam.com> wrote in message
> > > news:3fcfbc92$0$88383$75868355@news.frii.net...
> > > > I am importing a .csv file into Access that has 37 fields. My problem[/color][/color]
> is[color=green][color=darkred]
> > > > that sometimes the last field only has data at the end of the column[/color][/color]
> (it[color=green][color=darkred]
> > > > looks like when you import a file into Access, for the last field, it
> > > > only checks the top few 'cells' to see if there is any data, if not,[/color][/color]
> the[color=green][color=darkred]
> > > > field is not imported).
> > > >
> > > > How do I 'force' Access to import the field, regardless if there is[/color][/color]
> data[color=green][color=darkred]
> > > > in the top of the field or not? For instance, I might have 1,000[/color][/color]
> records[color=green][color=darkred]
> > > > that I am importing. If only the last 100 records have data in that
> > > > field, then, I lose the data because it is not importing the field.[/color][/color][/color]

I created the 'import spec' and saved it.

After I created it, now, in order to use the spec, every time I import a
file to this table, I have to go to advanced and specifically open it
EVERY TIME. (sometimes I could have a few files to import and it would
be easier to link the 'import spec' to this specific table).

How do you know link the 'import spec' to the table that it will be used
for?

Thanks for all of the responses and I hope someone can help me with this
problem, too,
Reaper

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Use the TransferText action (macro or code).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Grim Reaper" <reaper@nospam.com> wrote in message
news:3fd512e4$0$193$75868355@news.frii.net...[color=blue]
>
> How do you know link the 'import spec' to the table that it will be used
> for?[/color]

I setup 'TransferText' macro, but I am having one problem: when I use a
macro, I can not set it up to ask for a file name to import.

In other words, I would like the macro, when ran, to ask the user for a file
to import (every file name is different).

How do I setup the macro to ask the user for a file name to import (possibly
open a box that would allow the user to choose the file to import)?

Thanks,
Reaper

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:3fd5e429$0$1754$5a62ac22@freenews.iinet.net.a u...[color=blue]
> Use the TransferText action (macro or code).
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Grim Reaper" <reaper@nospam.com> wrote in message
> news:3fd512e4$0$193$75868355@news.frii.net...[color=green]
> >
> > How do you know link the 'import spec' to the table that it will be used
> > for?[/color]
>
>[/color]

Use the code in this link to pop up a FileOpen dialog and ask the user to
select a file:
http://www.mvps.org/access/api/api0001.htm
If you have never used an API call, it looks a little intimidating, but once
you have done it once it becomes as easy as using a function call.

Although it's possible to call the code in a macro, it really would be
better to use VBA for this. There are so many things that can go wrong with
an import, and the error handling would be very useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Grim Reaper" <73matt@comcast.net> wrote in message
news:_LydnW91Hs8KcEWiRVn-uw@comcast.com...[color=blue]
> I setup 'TransferText' macro, but I am having one problem: when I use a
> macro, I can not set it up to ask for a file name to import.
>
> In other words, I would like the macro, when ran, to ask the user for a[/color]
file[color=blue]
> to import (every file name is different).
>
> How do I setup the macro to ask the user for a file name to import[/color]
(possibly[color=blue]
> open a box that would allow the user to choose the file to import)?
>
> Thanks,
> Reaper
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:3fd5e429$0$1754$5a62ac22@freenews.iinet.net.a u...[color=green]
> > Use the TransferText action (macro or code).
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Grim Reaper" <reaper@nospam.com> wrote in message
> > news:3fd512e4$0$193$75868355@news.frii.net...[color=darkred]
> > >
> > > How do you know link the 'import spec' to the table that it will be[/color][/color][/color]
used[color=blue][color=green][color=darkred]
> > > for?[/color]
> >
> >[/color]
>
>[/color]