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! 11 3310
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" <re****@nospam.com> wrote in message
news:3f***********************@news.frii.net... 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.
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" <Al*********@SeeSig.Invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>... 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" <re****@nospam.com> wrote in message news:3f***********************@news.frii.net... 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.
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" <du********@aol.com> wrote in message
news:cc**************************@posting.google.c om... 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" <Al*********@SeeSig.Invalid> wrote in message
news:<3f**********************@freenews.iinet.net. au>... 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" <re****@nospam.com> wrote in message news:3f***********************@news.frii.net... 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.
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" <du********@aol.com> wrote in message
news:cc**************************@posting.google.c om... 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" <Al*********@SeeSig.Invalid> wrote in message
news:<3f**********************@freenews.iinet.net. au>... 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" <re****@nospam.com> wrote in message news:3f***********************@news.frii.net... 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. Al*********@SeeSig.Invalid (Allen Browne) wrote in
<3f**********************@freenews.iinet.net.au> : Select the file, and open it. Then choose the Advanced button in the next dialog.
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" <Al*********@SeeSig.Invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>... 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" <du********@aol.com> wrote in message news:cc**************************@posting.google.c om... 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" <Al*********@SeeSig.Invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>... 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" <re****@nospam.com> wrote in message news:3f***********************@news.frii.net... > 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.
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" <re****@nospam.com> wrote in message
news:3f*********************@news.frii.net... How do you know link the 'import spec' to the table that it will be used for?
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" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u... 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" <re****@nospam.com> wrote in message news:3f*********************@news.frii.net... How do you know link the 'import spec' to the table that it will be used for?
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" <73****@comcast.net> wrote in message
news:_L********************@comcast.com... 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" <Al*********@SeeSig.Invalid> wrote in message news:3f**********************@freenews.iinet.net.a u... 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" <re****@nospam.com> wrote in message news:3f*********************@news.frii.net... How do you know link the 'import spec' to the table that it will be
used for?
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: D Mat |
last post by:
Hi,
I'm trying to get MS Access 2000 to automatically import a series of
(~200) flat text, tab delimited, data files into a single Access table,
with consistent fields and rows.
The files have...
|
by: sparks |
last post by:
I have never done this and wanted to ask people who have what is the
best way.
One person said import it to excel, then import it into access table.
but since this will be done a lot, I am...
|
by: dixie |
last post by:
If I sent a user an empty database container - dB with no tables and I
needed them to import their tables into it and one of their tables was a
hidden table with the prefix Usys, is there any way...
|
by: hharriel |
last post by:
Hi,
I am hoping someone can help me with an issue I am having with excel
and ms access. I have collected data (which are in individual excel
files) from 49 different school districts. All...
|
by: mukeshsrivastav |
last post by:
dear sir
i want to move form excel to access.
i have 5 excel file having same formats and fields.now i want to import all data in one access table.
importing one file is easy .but importing and...
|
by: Debbiedo |
last post by:
I have a text file that I am importing into an Access table that was
generatred from data exported from a Word file. Several (about 20-30)
fields are from check boxes on the Word form. These fields...
|
by: Phil |
last post by:
I want to add a button to a form to import a text delimited file. The
File is delimited by "^" and so I have created a specification file by
manually impoting the text file, using the advanced...
|
by: Kosmos |
last post by:
Hey guys,
I'm fairly familiar with access by now, but I've never worked with importing text into an access database. I have some fairly large text files (lets say, for example, a folder of 20 text...
|
by: aconti74 |
last post by:
Hello I am new to vba programming/coding. I am writing a program that
goes through a directory of text files and imports them into the
database. The problem is eventually the database gets to big...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
| |