A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Importing Tab Delimited Text File to an Existing Table in Access



 
 
Thread Tools Display Modes
  #1  
Old February 2nd, 2010, 09:39 PM posted to microsoft.public.access.tablesdbdesign
Rita Brasher[_2_]
external usenet poster
 
Posts: 20
Default Importing Tab Delimited Text File to an Existing Table in Access

Office 2007, just upgraded from 2000 ( OMGOODNESS!! What a flippin'
NIGHTMARE!!)


I am simply trying to import a tab delimited text file into an existing
table in Access. In Office 2000, I was given an opportunity to change
the data type of columns to be imported during the import process
itself. In 2007, I am not given an option and end up with import errors
for every single row of data (over 1.5 million rows) because it reads a
text field of numbers as a number datatype. I can find no documentation
on how to solve my problem as Microsoft only provides information for
importing Excel files. Having well over a million rows, I cannot
maintain the data in an Excel file.


Thanks in advance for any help you can provide.

Rita Brasher
Project Engineer
FedEx Express


PS Here I thought I wasn't as productive as I could be because I was
behind on Office revisions. That was a joke!! Would maintaining a
semblance of similarity have been too much to ask??? If the simplest of
functions are going to all become difficult, I may have to go back to 2000.
  #2  
Old February 3rd, 2010, 01:20 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Importing Tab Delimited Text File to an Existing Table in Access

Rita

Often, the safer approach to import is to import data into a "temporary"
table, one you reserve for importing the data to.

Then you can run a series of queries the parse that raw import data into the
proper, well-normalized Access tables.

The problem with a lot of raw import tab delimited text file "data" is that
it is NOT well-normalized. If you're going to get the best use of Access
features/functions, you need to feed it data it works with best.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Rita Brasher" wrote in message
...
Office 2007, just upgraded from 2000 ( OMGOODNESS!! What a flippin'
NIGHTMARE!!)


I am simply trying to import a tab delimited text file into an existing
table in Access. In Office 2000, I was given an opportunity to change the
data type of columns to be imported during the import process itself. In
2007, I am not given an option and end up with import errors for every
single row of data (over 1.5 million rows) because it reads a text field
of numbers as a number datatype. I can find no documentation on how to
solve my problem as Microsoft only provides information for importing
Excel files. Having well over a million rows, I cannot maintain the data
in an Excel file.


Thanks in advance for any help you can provide.

Rita Brasher
Project Engineer
FedEx Express


PS Here I thought I wasn't as productive as I could be because I was
behind on Office revisions. That was a joke!! Would maintaining a
semblance of similarity have been too much to ask??? If the simplest of
functions are going to all become difficult, I may have to go back to
2000.



  #3  
Old February 3rd, 2010, 03:07 PM posted to microsoft.public.access.tablesdbdesign
Maarkr
external usenet poster
 
Posts: 240
Default Importing Tab Delimited Text File to an Existing Table in Access

when I use the wizard to import text files (External Data, Import text File)
and setup the import (using Advanced options if necessary), it offers many
different options to change the data type for each column, and then I can
build a query to filter out any junk or convert values and rename the fields.

Is this saved as a .csv file?

"Rita Brasher" wrote:

Office 2007, just upgraded from 2000 ( OMGOODNESS!! What a flippin'
NIGHTMARE!!)


I am simply trying to import a tab delimited text file into an existing
table in Access. In Office 2000, I was given an opportunity to change
the data type of columns to be imported during the import process
itself. In 2007, I am not given an option and end up with import errors
for every single row of data (over 1.5 million rows) because it reads a
text field of numbers as a number datatype. I can find no documentation
on how to solve my problem as Microsoft only provides information for
importing Excel files. Having well over a million rows, I cannot
maintain the data in an Excel file.


Thanks in advance for any help you can provide.

Rita Brasher
Project Engineer
FedEx Express


PS Here I thought I wasn't as productive as I could be because I was
behind on Office revisions. That was a joke!! Would maintaining a
semblance of similarity have been too much to ask??? If the simplest of
functions are going to all become difficult, I may have to go back to 2000.
.

  #4  
Old March 22nd, 2010, 08:46 PM posted to microsoft.public.access.tablesdbdesign
Rita Brasher[_2_]
external usenet poster
 
Posts: 20
Default Importing Tab Delimited Text File to an Existing Table in Access

These are .txt files, tab delimited. If I'm importing to an existing
table, which I am, I get NO options for changing data types. Hitting
the "Advanced" button only shows the fields to be imported with no data
type associated. I had NO problem importing in any of the previous
versions of Office.





On 2/3/2010 8:07 AM, Maarkr wrote:
when I use the wizard to import text files (External Data, Import text File)
and setup the import (using Advanced options if necessary), it offers many
different options to change the data type for each column, and then I can
build a query to filter out any junk or convert values and rename the fields.

Is this saved as a .csv file?

"Rita Brasher" wrote:

Office 2007, just upgraded from 2000 ( OMGOODNESS!! What a flippin'
NIGHTMARE!!)


I am simply trying to import a tab delimited text file into an existing
table in Access. In Office 2000, I was given an opportunity to change
the data type of columns to be imported during the import process
itself. In 2007, I am not given an option and end up with import errors
for every single row of data (over 1.5 million rows) because it reads a
text field of numbers as a number datatype. I can find no documentation
on how to solve my problem as Microsoft only provides information for
importing Excel files. Having well over a million rows, I cannot
maintain the data in an Excel file.


Thanks in advance for any help you can provide.

Rita Brasher
Project Engineer
FedEx Express


PS Here I thought I wasn't as productive as I could be because I was
behind on Office revisions. That was a joke!! Would maintaining a
semblance of similarity have been too much to ask??? If the simplest of
functions are going to all become difficult, I may have to go back to 2000.
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:49 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.