Multiple .CSV into One DB

[dbsavoy]

I've got 200+ excel spreadsheets (.csv) and I need to move them into one
access db to run pivottables. Excel does not have enough rows.

Douglass J. Steele wrote in a prior message:

Dim strFolder As String
Dim strFile As String
Dim strTable As String

strFolder = "C:\Some Folder\"
strFile = Dir(strFolder & "*.csv")
Do While Len(strFile) > 0
' Strip the .csv from the end of the file name
strTable = UCase(Left$(strFile, Len(strFile) - 4)
DoCmd.TransferText acImportDelim,, strTable, _
strFolder & strFile, True
strFile = Dir()
Loop

Please help me to edit the code above to process all CSV's into one DB...
I am not a programmer and only understand basics. I know I should copy the
code into a "Module"... and then I don't know the next step...

Thanks!

 

[wphx]

1. Create a new module
2. for help in debugging make the second line:

Type:

Option Explicit

3. Create a new function:
Type:

Public Function JoinTables(strFolder as string) as boolean

(this will also put a line 'End Function' in the module)

Between the Public Function ... end function copy and paste the code as
given. You could modify it slightly by removing the strFolder as string line
and strFolder=c:\some folder\ line

to run the code either:

1. Create a macro and put ina runcode command (in the macro section)

or

2. from the module window
View
Immediate Window (A window in which you can run VB instructions directly
without having to be in a module)

in the imediate window type:

print JoinTables("c:\Some Folder\")

this will force the function to be called and processed.

 

[dbsavoy]

Thanks, I've tried to run the code via the Immediate Window using:
print JoinTables("C:\Documents and Settings\usx19900\Desktop\Order and
Config Mix Data\")

I'm getting an error message stating:
Compile error:
Duplicate declaration in current scope

Here is the code I have in the Module section:
***
Option Compare Database

Option Explicit

Public Function JoinTables(strFolder As String) As Boolean
Dim strFolder As String
Dim strFile As String
Dim strTable As String

strFolder = "C:\Documents and Settings\usx19900\Desktop\Order and Config
Mix Data\"
strFile = Dir(strFolder & "*.csv")
Do While Len(strFile) > 0
' Strip the .csv from the end of the file name
strTable = UCase(Left$(strFile, Len(strFile) - 4)
DoCmd.TransferText acImportDelim, , strTable, _
strFolder & strFile, True
strFile = Dir()
Loop

End Function
***

 

[wphx]

if you prefer not to have to type in the folder name when you call the
function, just take the strFolder as string out of the

public Function JoinTables(strFolder as string) as boolean line

and leave in the references in the module

Thus:

public Function JoinTables(strFolder as string) as boolean

becomes

public Function JoinTables() as boolean


and call it by typing "print JoinTables()" in the immediate window

what is happening is that strFodler is being declared twice - once in the
function statement and once in the Dim statement - you need to have only one
or the other. If you want to keep it in the function statement, you will
have to remove the Dim strFolder statment and the strFolder="c:\..."
statement.

 

[dbsavoy]

Getting closer... Now I get an error message:

Compile error:
Syntax error

and the line of code highlighted is:

strTable = UCase(Left$(strFile, Len(strFile) - 4)

I appreciate all of your advice and your time. THANKS!

 

[wphx]

Had me scratching my head myself for a minite there - looked fine ....
except ..

you need a closing bracket for the ucase function i.e.:

strTable = UCase(Left$(strFile, Len(strFile) - 4))

I think it must be an optical illusion it was really awkward to spot.

you could also use ucase$ instead of ucase - no difference in your
application - ucase$ will return a string data type. as the arguments you
are using are always strings, you're not gaining anything using ucase.

 

[dbsavoy]

This is the string that never ends...

I've added the closing bracket and when I type in the Immediate Window:
print JoinTables()

It returns the word "False" in the Immediate Window.

Was there something I was supposed to have done prior to running this code?
Note that I have not created anything in Access aside from this module....
Do I need to create a table first? I thought this code would grab the CSVs
and import into a new table...

 

[wphx]

Sorry, I'm making assumptions about your level of experience.

A function always returns a value. the 'False' that you see is the value
returned by the function. It is displayed as 'False' because you decalred
the function to be a boolean function (as boolean) - a boolean value is
either 'true' or 'false'. You haven't explcitly given the function the value
to return so its simply returning 'false' as a default value.

It was probably more habit than anything that made me suggest the 'as
boolean' - you don't needf to worry about that. If your function worked, you
should see a group of tables in the 'tables' section in your database window
with the appropriate names. If nothing is there you need to look closer at
the import statement itself, or alternatively that the strFolder and strFile
values are being properly set.

Your code should read either:

***************************************************
Option Compare Database

Option Explicit

Public Function JoinTables() As Boolean
Dim strFolder As String
Dim strFile As String
Dim strTable As String

strFolder = "C:\Documents and Settings\usx19900\Desktop\Order and Config
Mix Data\"
strFile = Dir(strFolder & "*.csv")
Do While Len(strFile) > 0
' Strip the .csv from the end of the file name
strTable = UCase(Left$(strFile, Len(strFile) - 4))
DoCmd.TransferText acImportDelim, , strTable, _
strFolder & strFile, True
strFile = Dir()
Loop

End Function
*****************************************************

or
*******************************************
Option Compare Database

Option Explicit

Public Function JoinTables(strFolder As String) As Boolean
Dim strFile As String
Dim strTable As String

strFile = Dir(strFolder & "*.csv")
Do While Len(strFile) > 0
' Strip the .csv from the end of the file name
strTable = UCase(Left$(strFile, Len(strFile) - 4))
DoCmd.TransferText acImportDelim, , strTable, _
strFolder & strFile, True
strFile = Dir()
Loop

End Function

***************************

If you use the second option, you need to include the path to the folder
(including a '\' as the end of the path) when you call the function

i.e.

print JoinTables("C:\Documents and Settings\usx19900\Desktop\Order and
Config Mix Data\")

if you use the first option, all you need is

Print JoinTables()

other possibilities:
* check that folder does point to the right set of files - if there are no
..csv's in that folder, nothing will happen
* include the line:

debug.print strfolder & strfile,strtable

immediately before the docmd.trasfer line

if your file lookup is working properly, you will see the correct files
listed in your immediate window as you run the function (the debug.print is
the same as the print statement buts send the output to the immediate
window). if you are seeing the correct files displayed then look more
closely at the transfer command.

have fun

 

[dbsavoy]

Excellent! Thank you, wphx, you've saved me many hours of tedious work.

I've now got 200 tables in Access from my CSV files.

Now I need to get them into one table... Is there a way to do this? Or, do
I even need to do merge into one large table in order to create a pivottable
from all this data?

 

[dbsavoy]

I forgot to mention that field headings are identical among these tables and
the schema matches exactly as well.
Thx

 

[wphx]

1. Create a blank table to receive all your data - maybe easiest way is to
copy one of your tables, and then paste is, and choose to paste the
structure only.

2. Add two new fields to your table:

a) an ID of somesort with an autonumber datatype - becomes your primary key.
If there is alread an autonumber, You should make any existing autonumber
field a number/long integer

b) add a text field, to be long enough to hold the name of each of the
source tables

3. Normally the easiest way to join data is to use 'Append Queries' which
gives you the flexibility of reorganising fields from the source to target
tables. But to do that 200x could be a chore. If you know 100% that the
fields and data types match for all tables you could try something like
this:

(NB: This assumes the new table is called "NewTable", and you have a new ID
Field with a data type of 'autonumber' (primary key) and a text field named
'
SourceTable". Also that every table you are interested in has a prefix of
'xx' (you might want to change that line, it just prevents you from opening
the system tables or the newtable by accident when adding the tables). (I
don't know how this will be formatted in your news reader - there may be
some strange line breaks you'll have to sort out)


*******************************

Public Function mergetables()

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim rSource As DAO.Recordset
Dim rTarget As DAO.Recordset

Set db = CurrentDb()
Set rTarget = db.OpenRecordset("NewTable") '-- set up the target table

For Each tbl In db.TableDefs '-- scan every table in the database
If Left$(tbl.Name, 2) = "xx" Then '-- if the table is prefixed with
these letters, then add the contents to the target
Set rSource = db.OpenRecordset(tbl.Name) '-- open the source table
With rSource '-- this is just a shortcut - it means I don't have to
type rSource every time I want to refer to it (.bof, .eof etc)
If Not .BOF Then '== rSource.bof = Beginning of file - I use
this to determine an empty recordset
.MoveFirst '== rsource.movefirst
Do Until Not .EOF '-- read thru rSource
rTarget.AddNew '-- creat a new record in newtable
rTarget("SourceTable") = .Name '-- add the name of the
source table into the new table - this may help in identifying where your
data comes from in the future
For Each fld In .Fields '-- read every field in the
current record
rTarget(fld.Name) = fld.Value '-- assign the field
value to your new record in the target
Next
rTarget.Update '-- now write the new record in the
target
.MoveNext '-- get the next source record
Loop
End If
End With
rSource.Close '-- close the source table
End If
Next '-- do the next one

'-- finish up --

rTarget.Close
Set rSource = Nothing
Set rTarget = Nothing
Set db = Nothing


End Function

*******************************************

This one is a bit more elegant - it uses sql to do the same things as an
append query:
(NB: again you need to have a text field in the 'NewTable' table called
'SourceTable' and I strongly recommend a new ID field (autonumber type -
call it something different to any ID fields you have in the source tables.)

*********************************

Public Function mergetables2()

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim strSQL As String

Set db = CurrentDb()

For Each tbl In db.TableDefs '-- scan every table in the database
If Left$(tbl.Name, 2) = "xx" Then '-- if the table is prefixed with
these letters, then add the contents to the target
debug.print "Processing table: " & tbl.name
strSQL = "INSERT INTO NewTable ( SourceTable )" _
& " SELECT """ & tbl.Name & """ AS Expr1, " & tbl.Name & ".* " _
& " FROM " & tbl.Name & ";"
db.Execute strSQL
End If
Next '-- do the next one

'-- finish up --

Set tbl = Nothing
Set db = Nothing


End Function

*********************************

 

[dbsavoy]

This works beautifully. I really appreciate your time and the help given.

Cheers!

 

[wphx]

good to hear. all the best