Linking Files to a Database

I need to import data from 720 csv files into an Access database so I can do
some editing prior to loading into a SQL Server. These files came from data
output from a mainframe on a monthly basis. There are 15 files created each
month with each file containing specific data.

What I started to do is store the files by year and then by month so I new
when the data is based on. I have linked the table to the database and them
append the data into a table. Ok, this is ok for a file or two, but it is
really sucking for 720.

I tried to creating a table listing the location of each files and date of
production and then append the data into the appropriate table, however, I
could never get it to work. If anyone has any ideas or code samples, it
would be much appreciated.

Brian

re: Linking Files to a Database

"Brian" <jaxgtr01@bellsouth.net> wrote in message news:<UI0Sb.722$s2.365@bignews2.bellsouth.net>...[color=blue]
> I need to import data from 720 csv files into an Access database so I can do
> some editing prior to loading into a SQL Server. These files came from data
> output from a mainframe on a monthly basis. There are 15 files created each
> month with each file containing specific data.
>
> What I started to do is store the files by year and then by month so I new
> when the data is based on. I have linked the table to the database and them
> append the data into a table. Ok, this is ok for a file or two, but it is
> really sucking for 720.
>
> I tried to creating a table listing the location of each files and date of
> production and then append the data into the appropriate table, however, I
> could never get it to work. If anyone has any ideas or code samples, it
> would be much appreciated.
>
> Brian[/color]

Brian,
you can loop through folders and import the files. Use Dir to do the
looping and then create an import specification for each different CSV
file type so the data goes in the right table etc. I guess you could
create another record in what looks like a Location table and then
update at the end of each import file, so you'd just be updating the
[identifierLocation]=Null or whatever. Then you could just loop
through the folders and then the files within the folders and import.

re: Linking Files to a Database

Hi there,

Not too sure how familiar you are with vb?

The code to LINK a table is along the lines of:
DoCmd.TransferText acLinkDelim, "SpecificationsName", "NameOfTable", "LocationOfFile", False

You would probably need to create a loop to go through all of the files.

Hopefully you get this post before you've gone through manually and done the 720 files!!!

Regards,

Fraser.


"Brian" <jaxgtr01@bellsouth.net> wrote:[color=blue]
>I need to import data from 720 csv files into an Access database so I can do
>some editing prior to loading into a SQL Server. These files came from data
>output from a mainframe on a monthly basis. There are 15 files created each
>month with each file containing specific data.
>
>What I started to do is store the files by year and then by month so I new
>when the data is based on. I have linked the table to the database and them
>append the data into a table. Ok, this is ok for a file or two, but it is
>really sucking for 720.
>
>I tried to creating a table listing the location of each files and date of
>production and then append the data into the appropriate table, however, I
>could never get it to work. If anyone has any ideas or code samples, it
>would be much appreciated.
>
>Brian
>
>
>
>[/color]

re: Linking Files to a Database

I needed to add a few things to my email.

I have an Access Database setup using a form with simple code to run the
Append and Update Queries
to move the data to the appropriate table. What I want to do is have the
code look at the table I setup and
read the location and date and then run.

What I am doing now is Using the Link Table Manager and change the location
of the Link File, then
click the button on the form, it runs the Append and Update Queries then
Opens the Link Table Manager
Window back up for me. This is where I want to be able to read from the
table of where my files locations
are and the date of the data run. Then once i press the button is will loop
through all 48 months of data at one
swoop. I just cant get my code right to get the data from the table into
the code. I have done this before, but
I am obviously missing something. Frustration has set in and now I just
tired of screwing with it.

DB Info:
DB Name: DW Import
Table Name: DISTRPT
Field Name: Location Example Data:
"Z:\2000\0100\distrpt.csv"
Field Name: Date Example Datra: 01/28/2000

Brian






"Brian" <jaxgtr01@bellsouth.net> wrote in message
news:UI0Sb.722$s2.365@bignews2.bellsouth.net...[color=blue]
> I need to import data from 720 csv files into an Access database so I can[/color]
do[color=blue]
> some editing prior to loading into a SQL Server. These files came from[/color]
data[color=blue]
> output from a mainframe on a monthly basis. There are 15 files created[/color]
each[color=blue]
> month with each file containing specific data.
>
> What I started to do is store the files by year and then by month so I new
> when the data is based on. I have linked the table to the database and[/color]
them[color=blue]
> append the data into a table. Ok, this is ok for a file or two, but it is
> really sucking for 720.
>
> I tried to creating a table listing the location of each files and date of
> production and then append the data into the appropriate table, however, I
> could never get it to work. If anyone has any ideas or code samples, it
> would be much appreciated.
>
> Brian
>
>
>
>[/color]

re: Linking Files to a Database

The only suggestion I can think of is if you have the values stored in a table, of the filenames and locations - and use that to loop through the files, linking them, and importing the data appropriately.

I'm assuming however;
- All files have same structure
- All files get imported in the same way.

If these assumptions are not correct, then you would need to have different loops (depending on how you want to code it) and different import specs (and queries = import procedure) for each of the different types of files. For example, if there are 15 different types of files, you would need to put in 15 loops, or a select case within the loop that has 15 cases.

A few other tips, as it is very difficult to interpret what it is people are trying to do without seeing it in front of you;
You could set up loops that increment by 1.
Using those increments, work out the month you want to import (dateadd("m","Start Date",intIncrementValue) will return the number of months inline with the increment.
A similar idea can be done with a year value as well.
This should enable you to piece together the name(s) of the files.
e.g. January_2004_file1.csv
dateadd("m",1/1/04,1) = 1/2/04 -> Format(1/2/04,"mmmm") = February
(not using american date format here!)
use those values in a string to workout the filepath.

Use the previous line of code for linking the table, make sure you call the table the same name, and then run your queries as part of the import procedure you have designed.

Then move onto the next increment for the loop...

If you're not too frustrated, hopefully that helps.

Regards,

Fraser.

"Brian" <jaxgtr01@bellsouth.net> wrote:[color=blue]
>I needed to add a few things to my email.
>
>I have an Access Database setup using a form with simple code to run the
>Append and Update Queries
>to move the data to the appropriate table. What I want to do is have the
>code look at the table I setup and
>read the location and date and then run.
>
>What I am doing now is Using the Link Table Manager and change the location
>of the Link File, then
>click the button on the form, it runs the Append and Update Queries then
>Opens the Link Table Manager
>Window back up for me. This is where I want to be able to read from the
>table of where my files locations
>are and the date of the data run. Then once i press the button is will loop
>through all 48 months of data at one
>swoop. I just cant get my code right to get the data from the table into
>the code. I have done this before, but
>I am obviously missing something. Frustration has set in and now I just
>tired of screwing with it.
>
>DB Info:
>DB Name: DW Import
>Table Name: DISTRPT
>Field Name: Location Example Data:
>"Z:\2000\0100\distrpt.csv"
>Field Name: Date Example Datra: 01/28/2000
>
>Brian
>
>
>
>
>
>
>"Brian" <jaxgtr01@bellsouth.net> wrote in message
>news:UI0Sb.722$s2.365@bignews2.bellsouth.net...[color=green]
>> I need to import data from 720 csv files into an Access database so I can[/color]
>do[color=green]
>> some editing prior to loading into a SQL Server. These files came from[/color]
>data[color=green]
>> output from a mainframe on a monthly basis. There are 15 files created[/color]
>each[color=green]
>> month with each file containing specific data.
>>
>> What I started to do is store the files by year and then by month so I new
>> when the data is based on. I have linked the table to the database and[/color]
>them[color=green]
>> append the data into a table. Ok, this is ok for a file or two, but it is
>> really sucking for 720.
>>
>> I tried to creating a table listing the location of each files and date of
>> production and then append the data into the appropriate table, however, I
>> could never get it to work. If anyone has any ideas or code samples, it
>> would be much appreciated.
>>
>> Brian
>>
>>
>>
>>[/color]
>
>[/color]

re: Linking Files to a Database

I figured out my problem. For what ever reason, the DAO Reference Library
was not selected. Once I checked that, all seemed to work like it should
have.

Never seen that before, never even thought to check it. That is usually
something that is automatically done. Thanks.

Brian


"4Fraza" <fraza@clear.net.nz> wrote in message news:40199c7b@clear.net.nz...[color=blue]
>
> The only suggestion I can think of is if you have the values stored in a[/color]
table, of the filenames and locations - and use that to loop through the
files, linking them, and importing the data appropriately.[color=blue]
>
> I'm assuming however;
> - All files have same structure
> - All files get imported in the same way.
>
> If these assumptions are not correct, then you would need to have[/color]
different loops (depending on how you want to code it) and different import
specs (and queries = import procedure) for each of the different types of
files. For example, if there are 15 different types of files, you would
need to put in 15 loops, or a select case within the loop that has 15 cases.[color=blue]
>
> A few other tips, as it is very difficult to interpret what it is people[/color]
are trying to do without seeing it in front of you;[color=blue]
> You could set up loops that increment by 1.
> Using those increments, work out the month you want to import[/color]
(dateadd("m","Start Date",intIncrementValue) will return the number of
months inline with the increment.[color=blue]
> A similar idea can be done with a year value as well.
> This should enable you to piece together the name(s) of the files.
> e.g. January_2004_file1.csv
> dateadd("m",1/1/04,1) = 1/2/04 -> Format(1/2/04,"mmmm") = February
> (not using american date format here!)
> use those values in a string to workout the filepath.
>
> Use the previous line of code for linking the table, make sure you call[/color]
the table the same name, and then run your queries as part of the import
procedure you have designed.[color=blue]
>
> Then move onto the next increment for the loop...
>
> If you're not too frustrated, hopefully that helps.
>
> Regards,
>
> Fraser.
>
> "Brian" <jaxgtr01@bellsouth.net> wrote:[color=green]
> >I needed to add a few things to my email.
> >
> >I have an Access Database setup using a form with simple code to run the
> >Append and Update Queries
> >to move the data to the appropriate table. What I want to do is have the
> >code look at the table I setup and
> >read the location and date and then run.
> >
> >What I am doing now is Using the Link Table Manager and change the[/color][/color]
location[color=blue][color=green]
> >of the Link File, then
> >click the button on the form, it runs the Append and Update Queries then
> >Opens the Link Table Manager
> >Window back up for me. This is where I want to be able to read from the
> >table of where my files locations
> >are and the date of the data run. Then once i press the button is will[/color][/color]
loop[color=blue][color=green]
> >through all 48 months of data at one
> >swoop. I just cant get my code right to get the data from the table into
> >the code. I have done this before, but
> >I am obviously missing something. Frustration has set in and now I just
> >tired of screwing with it.
> >
> >DB Info:
> >DB Name: DW Import
> >Table Name: DISTRPT
> >Field Name: Location Example Data:
> >"Z:\2000\0100\distrpt.csv"
> >Field Name: Date Example Datra: 01/28/2000
> >
> >Brian
> >
> >
> >
> >
> >
> >
> >"Brian" <jaxgtr01@bellsouth.net> wrote in message
> >news:UI0Sb.722$s2.365@bignews2.bellsouth.net...[color=darkred]
> >> I need to import data from 720 csv files into an Access database so I[/color][/color][/color]
can[color=blue][color=green]
> >do[color=darkred]
> >> some editing prior to loading into a SQL Server. These files came from[/color]
> >data[color=darkred]
> >> output from a mainframe on a monthly basis. There are 15 files created[/color]
> >each[color=darkred]
> >> month with each file containing specific data.
> >>
> >> What I started to do is store the files by year and then by month so I[/color][/color][/color]
new[color=blue][color=green][color=darkred]
> >> when the data is based on. I have linked the table to the database and[/color]
> >them[color=darkred]
> >> append the data into a table. Ok, this is ok for a file or two, but it[/color][/color][/color]
is[color=blue][color=green][color=darkred]
> >> really sucking for 720.
> >>
> >> I tried to creating a table listing the location of each files and date[/color][/color][/color]
of[color=blue][color=green][color=darkred]
> >> production and then append the data into the appropriate table,[/color][/color][/color]
however, I[color=blue][color=green][color=darkred]
> >> could never get it to work. If anyone has any ideas or code samples,[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> >> would be much appreciated.
> >>
> >> Brian
> >>
> >>
> >>
> >>[/color]
> >
> >[/color]
>[/color]