How can I import 2 csv files into 1 table

[ninrulz]

I will try to explain my situation. I know that it is hard to offers
solutions without fully understanding what people would like to
achieve.

I receive 2 csv files every month. The csv files change name each
month. Each file contains 13 columns and around 20k rows. In each file,
the first row is the heading.

I have created a database that helps me sort out the data. Prior to
importing into the database, I have been copying the csv data into one
xls file. I then run an Excel macro to format the data. The macro does
a few things. The first thing it does is remove any blank spaces in the
text of column A. Then the macro changes the format of column I to
number with no decimals and changes the format of column L to short
date. I then run an Access macro to import the xls file into a table
and do a number of other things before spitting out the 72 Excel files
I use for reporting. I have formatted the table to accept the data from
the xls file. For example, Column J contains notes, which exceeded the
default character length. The notes were truncated when I tried to
import the csv file straight to a table. Column I contains numbers and
text which were left blank when I tried to import the csv into a table.

I would like to incorporate the formatting and importation of these
files into the macro that I already use to sort and export 72 Excel
files. I would like to place the 2 csv files in a directory and have
Access import the files into a single table. Both csv files always
start with the same title (file_name ***.csv). The csv files should be
imported from a different directory than the target for the exported
xls files.

I am not a programmer. I have been stumbling through this process for
about a week now. The purpose for the database is to make my job
easier, but I have also been enjoying the challenge. Let me know if you
can help me out or if I can offer more clarification.

Thanks,
Mark

[pietlinden]

ninrulz wrote:

I will try to explain my situation. I know that it is hard to offers
solutions without fully understanding what people would like to
achieve.

I receive 2 csv files every month. The csv files change name each
month. Each file contains 13 columns and around 20k rows. In each file,
the first row is the heading.

use TransferText

I would like to incorporate the formatting and importation of these
files into the macro that I already use to sort and export 72 Excel
files. I would like to place the 2 csv files in a directory and have
Access import the files into a single table. Both csv files always
start with the same title (file_name ***.csv). The csv files should be
imported from a different directory than the target for the exported
xls files.

You don't store formatted data in a database. That's for presentation.
Importing is in a different folder? So? use an import spec and maybe
specify the path for input and output in a table or something (so you
can modify/store it).

[ninrulz]

The script I am using is below. There are two additional issues I need
to overcome.

Function ImportFiles()
Dim strPath As String
Dim strFile As String
strPath = "C:\Source Files\"
strFile = Dir$(strPath & "*.csv")
Do While Len(strFile) 0
DoCmd.TransferText acImportDelim, , "Monthly Report -
unsorted", strPath & strFile, True
strFile = Dir$
Loop
End Function

1.) One of the fields gets a few truncation errors when I import. There
are over 400 characters in some of the cells of one column. I need to
do one of 2 things. A.) Increase the size or change the format of the
field to accommodate the data, or B.) Because I do not really care
about the truncated data, prevent the creation of the ImportErrors
Tables.

Error Field Row
Field Truncation Notes 8364
Field Truncation Notes 9617
Field Truncation Notes 12948
Field Truncation Notes 17610

2.) Remove the blank spaces from the first column of each csv file,
prior to importing it. The first row of each csv file is the heading
and the spaces need to be removed from that as well.

Example:
Heading Name Example changed to HeadingNameExample

I would like to automate the import of these csv files without having
to make any changes to them. Is there a way to makes these changes as
part of the script?

Thank you,
Mark

pi********@hotmail.com wrote:

ninrulz wrote:

I will try to explain my situation. I know that it is hard to offers
solutions without fully understanding what people would like to
achieve.

I receive 2 csv files every month. The csv files change name each
month. Each file contains 13 columns and around 20k rows. In each file,
the first row is the heading.

use TransferText

I would like to incorporate the formatting and importation of these
files into the macro that I already use to sort and export 72 Excel
files. I would like to place the 2 csv files in a directory and have
Access import the files into a single table. Both csv files always
start with the same title (file_name ***.csv). The csv files should be
imported from a different directory than the target for the exported
xls files.

You don't store formatted data in a database. That's for presentation.
Importing is in a different folder? So? use an import spec and maybe
specify the path for input and output in a table or something (so you
can modify/store it).

[Jeff]

Mark

Maybe you should import into a buffer table first, then run one or more
queries to clean up the data, then another append query to finally put your
data into the real table. The field with a large amount of text could go
into a memo field and your append query could truncate the data if required
using something like the Left function.

You buffer table could have all fields as basic text so there are no rules
to worry about, nothing required. That way you file will always import. Then
worry about the validity of the data.

That would be my approach. In that way you can even run a validation routine
against the data before you commit it to your very valuable real data.

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

"ninrulz" <ni********@hotmail.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

The script I am using is below. There are two additional issues I need
to overcome.

Function ImportFiles()
Dim strPath As String
Dim strFile As String
strPath = "C:\Source Files\"
strFile = Dir$(strPath & "*.csv")
Do While Len(strFile) 0
DoCmd.TransferText acImportDelim, , "Monthly Report -
unsorted", strPath & strFile, True
strFile = Dir$
Loop
End Function

1.) One of the fields gets a few truncation errors when I import. There
are over 400 characters in some of the cells of one column. I need to
do one of 2 things. A.) Increase the size or change the format of the
field to accommodate the data, or B.) Because I do not really care
about the truncated data, prevent the creation of the ImportErrors
Tables.

Error Field Row
Field Truncation Notes 8364
Field Truncation Notes 9617
Field Truncation Notes 12948
Field Truncation Notes 17610

2.) Remove the blank spaces from the first column of each csv file,
prior to importing it. The first row of each csv file is the heading
and the spaces need to be removed from that as well.

Example:
Heading Name Example changed to HeadingNameExample

I would like to automate the import of these csv files without having
to make any changes to them. Is there a way to makes these changes as
part of the script?

Thank you,
Mark

pi********@hotmail.com wrote:

>ninrulz wrote:

I will try to explain my situation. I know that it is hard to offers
solutions without fully understanding what people would like to
achieve.

I receive 2 csv files every month. The csv files change name each
month. Each file contains 13 columns and around 20k rows. In each file,
the first row is the heading.

use TransferText

I would like to incorporate the formatting and importation of these
files into the macro that I already use to sort and export 72 Excel
files. I would like to place the 2 csv files in a directory and have
Access import the files into a single table. Both csv files always
start with the same title (file_name ***.csv). The csv files should be
imported from a different directory than the target for the exported
xls files.

You don't store formatted data in a database. That's for presentation.
Importing is in a different folder? So? use an import spec and maybe
specify the path for input and output in a table or something (so you
can modify/store it).