PDA

View Full Version : Importing into MS Access. Any ideas?


Rain Man
05-10-2005, 12:11 PM
I've got a really big Access database with lots of records and stuff, and it took me a long time to get all of the data records parsed.

Now I've got about 50 other files that have exactly the same data format, but they're text files that contain one 5,000 character string of numbers for each data record.

Does anyone know how I can move the data structure for the existing file into a new file and import a new set of data, without having to set up the structure again from scratch?

I thought I could just copy the file, delete out all of the data, and then do an import, but when I do that, the whole 5,000 character string just goes into the first record.

Simplex3
05-10-2005, 12:52 PM
I've got a really big Access database with lots of records and stuff, and it took me a long time to get all of the data records parsed.

Now I've got about 50 other files that have exactly the same data format, but they're text files that contain one 5,000 character string of numbers for each data record.

Does anyone know how I can move the data structure for the existing file into a new file and import a new set of data, without having to set up the structure again from scratch?

I thought I could just copy the file, delete out all of the data, and then do an import, but when I do that, the whole 5,000 character string just goes into the first record.
Sounds like you got your delimiter wrong during the import.

KCWolfman
05-11-2005, 09:18 PM
Can you convert the text to an Excel file?

At that point you could place your delimiters anywhere you want and import to the database.

StcChief
05-15-2005, 07:55 PM
Ues a real database.

MySQL if you want to work on the cheap.

IF you are hung up using M$oft go with SQL Server.

You are going to fight the size limit forever with Access.
Multiuser forget it.

KC Jones
05-16-2005, 08:02 AM
Ues a real database.

MySQL if you want to work on the cheap.

IF you are hung up using M$oft go with SQL Server.

You are going to fight the size limit forever with Access.
Multiuser forget it.

That's kinda what I was thinking, but it's not exactly pertinent to his question about importing data. Access is a great lil DB tool for small amounts of data and though I personally loathe VB, it provides a nice little RAD gui for building simple applications.

StcChief
05-20-2005, 01:44 PM
Need to save the file in .csv (comma seperated value) format excel/access can then read that file

Rain Man
05-20-2005, 01:57 PM
Need to save the file in .csv (comma seperated value) format excel/access can then read that file

I'd have to manually make the delimiters. What I've got now is a million lines of data, and each line is an unbroken strong of 500 or so characters.

Simplex3
05-20-2005, 02:17 PM
I'd have to manually make the delimiters. What I've got now is a million lines of data, and each line is an unbroken strong of 500 or so characters.
So is the carriage return your delimiter? Is the entire 500 charaters one data row? Basically a one column table?

Rain Man
06-02-2005, 10:33 PM
Yep. 500 characters in one row.

One of my employees figured it out. There's an option buried somewhere to import using a "template" or some such thing. "Template" is not the word, but I can't remember what it is. By clicking that, everything imports nice and neat. Hip hip ... hooray!

KC Jones
06-03-2005, 12:42 AM
Yep. 500 characters in one row.


Wow, well unless it's all one logical and indivisible data field you may want to look up something called database normalization or have a DBA take a crack at designing a DB schema for you. Sounds to me like you have one massive flat file, which in many ways would be defeating the purpose of using a RDBMS.