Table Import Help

[GoodOmens]

I have lots of CSV files that are per day and are as follows:

File 1:

Username Time
Bob 2
Joe 6

File 2:
Username Time
Joe 8
Sue 6

Essentially what I want to do is import each file into one table where if the username is not in the database it creates it, if its already in there a new column is created with the date. I dont mind creating multiple tables for each date and merging them.

So after those two files above were imported my table would look like this:

Username Time1 Time2
Bob 2 0
Joe 6 8
Sue 0 6

There are not many individual dates for that to be a problem, but there are over 5k different usernames in each file so I can't eyeball anything lol.

I am fairly new to access, but I am seasoned in MYSQL and Oracle. I've done some light searching on these forums and have not really come accross anything I need.

Thanks!

 

[KevinM]

You need to create a seperate table for your 'Times' rather than seperate columns in one table so as to 'normalise' your data and create a 'one to many' relationship between the two tables on 'UserName'.

 

[Jibbadiah]

Create the table with your first file. Add a new field to the table called Update_Date. Click on the properties tab of that table. Set the name field as your Primary Key. Create an append query to append to this table. The key will drop out any duplicates and append any new records. Your append query should include the new field with criteria of UPDATE_DATE: Format(Now(),"dd/mm/yyyy").

 

[GoodOmens]

Create the table with your first file. Add a new field to the table called Update_Date. Click on the properties tab of that table. Set the name field as your Primary Key. Create an append query to append to this table. The key will drop out any duplicates and append any new records. Your append query should include the new field with criteria of UPDATE_DATE: Format(Now(),"dd/mm/yyyy").

I tried exactly this, but as you already stated it only added new records, it did not add anything to usernames that already existed due to key violations.

Thanks for the quick replies!

Now what I have is seperate tables with each "time" for each day.

Each table is as follows:
Table day1
USERNAME DAY1
Table day2:
USERNAME DAY2
Table day3:
USERNAME DAY3
etc ...
The username field will contain duplicates because he may be active for more then one day. Like I said earlier I am trying to merge everything into one table (or one query I can export) where duplicate usernames are merged into one row. So it would be:
USERNAME DAY1 DAY2 DAY3

 

[Jibbadiah]

I have to get going... but if you want to run something like the following in VB, then it should create like a cross-tab query with name on the vertical axis and date on the horizontal, and for each name/date combination it will populate the cell with an "X".... hopefully. There might be a couple of bugs... but give me a shout and I can look again tomorrow.

Function Good_Omens()
Dim dbs As Database
Dim rst_NEW As Recordset
Dim Today As Date
Dim strSQL1
Dim strSQL2 As String
Dim NewColumn As String
Dim CurrentName As String

DoCmd.SetWarnings False

'Define new column name
Today = (Format(Now(), "dd/mm/yyyy"))
NewColumn = Left(Today, 2) & Format(Today, "mmm") & Right(Today, 2)
'Create new column based on todays date
strSQL1 = "ALTER TABLE <MAIN_TABLE> ADD COLUMN " & NewColumn & " TEXT;"
DoCmd.RunSQL (strSQL1)

Set dbs = CurrentDb
Set rst_NEW = dbs.OpenRecordset("SELECT NAME FROM <IMPORT_TABLE>, <MAIN_TABLE> WHERE IMPORT_TABLE.NAME = MAIN_TABLE.NAME;")

With rst_NEW
.MoveFirst
Do While Not rst_NEW.EOF
CurrentName = (rst_NEW("NAME"))

' For each NAME that exists pop an X in the new date column
strSQL2 = "UPDATE MAIN_TABLE SET " & NewColumn & " = 'X' WHERE IMPORT_TABLE.NAME = '" & CurrentName & "';"
DoCmd.RunSQL (strSQL2)
.MoveNext
Loop

End With

End Function

 

[GoodOmens]

Hmm cool!

I managed to figure out ... I imported one of the sheets into access then added the columns I needed.

I then imported each data sheet as a seperate table.

I then did the append query to the first table to get any usernames not in the first table.

Lastly I did a update table from each table to append the data to the columns.

Your way looks much cleaner and I will look into it !

 

[Jibbadiah]

Couldn't agree with you more Pat... but the reason I came up with that code in the first place was to essentially create a cross tab query that folks could view easily, updated itself automatically (so I didn't have to keep a record manually), was easy to output, and completely maintenance free... having said that my table will only ever have around 50 rows (max)... which is a much different scenario to GoodOmens... and I only add a new column once a week. I was just giving him what he asked for - but agree it is VERY wrong for his scenario!! GoodOmen - you should be ashamed of yourself!! ;-)

 

[Jibbadiah]

Yeah... been playing too much Americas Army... shooting too many people is becoming a habit... sorry guys

 

[GoodOmens]

Adding new columns dependent on data is ABSOLUTELY WRONG!!!! You are creating a table as you would create a spreadsheet. Tables ARE NOT spreadsheets. You and Jibbadiah both need to do some reading on normalization. You have a 1-many relationship. That requires two tables to properly implement. The one-side table holds the user information because that occurs only once and the many-side table holds the time information because that occurs many times for each user. Each separate time entry becomes a row in the many-side table.

Ahh thats a good approach. I'll see if I can import my data that way ... unfortunatly I am tied to how the data is given to me as csv files.

 

[ByteMyzer]

Create a single table named tblTimeSheet, with the following fields:
UserName (Data Type: Text)
UserDate (Data Type: Date/Time)
UserTime (Data TYpe: Number - Field Size: Long Integer)

Set Indexes on fields UserName and UserDate, with Duplicates allowed. No primary key is required.


Next create a query with the following SQL and save it as qdfTimeSheet:

TRANSFORM First(tblTimeSheet.UserTime)
SELECT tblTimeSheet.UserName
FROM tblTimeSheet
GROUP BY tblTimeSheet.UserName
PIVOT tblTimeSheet.UserDate;

Finally, copy and paste the text from the following code into a new Module:

Function TimeSheet_Add(fileName As String) As Boolean

    Dim vLine As Variant
    Dim vStr As String

    Open fileName For Input As #1

    'Discard the headers
    Input #1, vStr
    vLine = Split(vStr, ",")

    'Loop through the remaining lines
    Do While Not EOF(1)
        Input #1, vStr
        vLine = Split(vStr, ",")
        CurrentDb.Execute "INSERT INTO tblTimeSheet (UserName,UserDate,UserTime) " _
            & "SELECT '" & vLine(0) & "',Date()," & vLine(1) & ";"
    Loop

    Close #1

End Function

Then you can call the function with something like:

Call TimeSheet_Add("C:\MyTimeSheet.csv")

and the entries from the file will be added to the table.

DISCLAIMER: The above code assumes a layout in your file like:
Username,Time
Bob,2
Joe,6
No error traps have been included with this code sample. I leave the refinements of error-trapping and such to you. I hope this is enough to start you on the right track.


Now you can open the query qdfTimeSheet and view the results in your desired layout.

See if this works for you.

 

[KevinM]

Ahh thats a good approach. I'll see if I can import my data that way ... unfortunatly I am tied to how the data is given to me as csv files.

I guess you missed my reply saying EXACTLY the same thing

A classic example of Pat Hartman's impecccable reputation.

A 'nod' to my reply from Pat wouldn't have gone a miss.