Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-06-2012, 06:58 AM   #1
Syllinger
Newly Registered User
 
Join Date: May 2012
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Syllinger is on a distinguished road
Lightbulb CSV mass import...with a twist (unforunately).

Hey folks,

I have about 30 different master tables and there are about 100 individual data files that need to be inserted into each master.

So far, I have created a macro in excel to list all of the possible field names in the data files, and I have dervived my my master tables from the output.

So, why did I need to do this? Here's the unfortunate twist. The individual data tables all list only the fields that contain data in alphabetical order. There are approximately 40 fields total across all of the data files, but each file usually only contains about 25 fields. Don't ask me why they are formatted this way, I can't figure out the logic either.

My issue is that I now have to get the data from these 1200 tables individual tables and organize them into their respective master.

So, I imagine I have two methods of doing this. I can either clean up the data in Excel trying to create some kind of standard template that I can mass upload using an import spec, but I would prefer to do all of the legwork in Access.

Has anyone ever attempted this, or know of a method by which to do this?

Finally, to throw another curve-ball your way there are several fields that need to be changed from "Text" to "Memo" as they contain more than 255 characters.

Any help/ideas about how to approach this would be appreciated.

Thanks for reading!

Syllinger is offline   Reply With Quote
Old 05-06-2012, 08:44 AM   #2
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: CSV mass import...with a twist (unforunately).

can you clarify this again

the header row defines how many fields, and the field order?
are the data files csvs?

anyway, definitley do not touch them with excel. excel can actually change data. you are better keeping the raw data intact

you need to understand what you have.

in this case, probably do the import manually

read the header.
store the column heads in an array

read the data lines a row at a time
write a sql statement to insert the row into the correct table(s) based on the structure of the header.

you need to make sure your table structure works for all variations of data file you arel ikely to get.

you may be able to import directly to a single master table, as long as all the column headers are present.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-06-2012, 10:39 AM   #3
Syllinger
Newly Registered User
 
Join Date: May 2012
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Syllinger is on a distinguished road
Re: CSV mass import...with a twist (unforunately).

Hi Gemma. Thank you for the response!

1. I have 400ish folders named according to each individual customer.

2. Each folder contains data files named according to the table the data belongs to.

3. The field names for each data file are found in the first row in alphabetical order, BUT only the fields that contain data are included in the extract.

This example should highlight what I am talking about:

Folder structure:
C:\ABC Company\CustomerInfo.csv
C:\ABC Company\OrderInfo.csv
C:\XYZ Co\CustomerInfo.csv
C:\XYZ Co\OrderInfo.csv

File structure:
C:\ABC Company\CustomerInfo.csv contains:
"Field1","Field3","Field5"

C:\XYZ Co\CustomerInfo.csv contains:
"Field2","Field4"

CustomerInfo (master table) contains:
"Field1","Field2","Field3""Field4","Field5"

As you can see, the master table contains all field names across all files found in the 400ish folders. I created this table using the excel macro I described in the original post.

Your approach sounds bang on, but how do I do that? Do I create a VBA macro similar to the one I did in excel that will open every similarly named CSV across all folders, save the field names to an array, and then write INSERT statements for each row based on the field name configuration?

I.E. Open all CustomerInfo files and INSERT depending on what is in the first row?

For C:\ABC Company\CustomerInfo.csv:
INSERT INTO CustomerInfo (Field1,Field3,Field5)
VALUES (...)

For C:\XYZ Co\CustomerInfo.csv:
INSERT INTO CustomerInfo (Field2,Field4)
VALUES (...)

Something like this?

Syllinger is offline   Reply With Quote
Old 05-06-2012, 12:14 PM   #4
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: CSV mass import...with a twist (unforunately).

you could try designing a standard table, with all 5 fields

then try to design an importspec that will import each variant, even though some columns are missing

you will have to try this to see if it works

if it does then you can just do

docmd.transfertext and do the whole thing (for each file) with one line of code

i don;t quite understand why sone columns of data are missing, if theyt are all important

.....

if you find you cannot import the whole thing - then the trickier alternative is to read successive lines, and write the sql insert statements
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-06-2012, 12:31 PM   #5
Syllinger
Newly Registered User
 
Join Date: May 2012
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Syllinger is on a distinguished road
Re: CSV mass import...with a twist (unforunately).

The reason that all fields are not being pulled is becuase the person extracting the information is incompetent, and apparently this is the only way to pull it out of the current database.

First, I thought that using a loop to insert the data record by record would work, but I feel that this is going to create some masssive program overhead.

Is is not possible to simply have dynamic INSERT INTO statements generated via VBA?
Syllinger is offline   Reply With Quote
Old 05-06-2012, 01:55 PM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: CSV mass import...with a twist (unforunately).

You can let the query engine help you with this by creating a generic append query and letting it figure out all the columns. Of course this will ONLY work if the column names are IDENTICAL in all the tables.
Code:
INSERT INTO [yourTotablename]
SELECT [yourFromTablename].*
FROM [yourFromTablename];

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Tags
csv , import , mass

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Subtraction with a twist Fairman Queries 3 12-04-2011 02:17 PM
First-Last(or something like that) With a twist... AggieLJ Queries 2 04-01-2009 02:28 PM
mass update toddbingham Forms 2 08-29-2003 06:31 AM
twist on addition ppoole16 Forms 5 08-21-2003 11:30 PM
Mass update joe31709 Queries 8 08-21-2003 05:52 AM




All times are GMT -8. The time now is 12:57 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World