Import CSV Problem

[AC5FF]

I've done countless imports into DBs in the past and never had issues before today. Not sure what is happening here.

Below is a sample of the data I am trying to import. Its exported from another program that I have zero control over and cannot change any formatting.

I've tried to pre-set up my table with properly formatted columns (i.e. Medium Date/Time, etc...) and that has not helped. I've also tried to import the dates and times as plane text with no help.

I will keep getting a Conversion Error on fields 4 and 5. Or other times it locks up Access and I have to restart the program. I've not been able to determine why one time it gives me the errors and other times it locks up.

Just seems weird - never had imports give me this many headaches in the past...

<NUMBER>,<ECODE>,<DATEIN>,<TIMEIN>,<TIMEOUT>,<REGHOURS>
7,AJW,8/31/2015,9:15 AM,12:01 PM,2.77
7,AJW,8/31/2015,12:31 PM,4:57 PM,4.43
7,AJW,9/1/2015,8:02 AM,12:08 PM,4.1
7,AJW,9/1/2015,12:35 PM,4:56 PM,4.35
7,AJW,9/2/2015,8:06 AM,12:02 PM,3.93
7,AJW,9/2/2015,12:32 PM,4:58 PM,4.43
7,AJW,9/3/2015,8:05 AM,12:00 PM,3.92
7,AJW,9/3/2015,12:30 PM,3:30 PM,3
7,AJW,9/4/2015,7:57 AM,12:04 PM,4.12
7,AJW,9/4/2015,12:31 PM,3:00 PM,2.48
7,AJW,9/4/2015,3:00 PM,5:00 PM,2

 

[plog]

Divide and conquer. Strip out everything but 1 record from that file and import it. If it works its a bad record somewhere in there--keep adding records to the file until you find the offender.

If it won't import 1 record, then its the file itself--try converting it to a different type (text, cvs, excel, etc.)

 

[AC5FF]

Odd...
I saved the CSV as an XLS file and no errors at all doing the import.


Now to see if I can manipulate the data correctly... Probably should be its own thread but I'll ask here real quick..

In my sample data above, just take the first two lines:
7,AJW,8/31/2015,9:15 AM,12:01 PM,2.77
7,AJW,8/31/2015,12:31 PM,4:57 PM,4.43

I need to convert this to look like:
7,AJW,8/31/2015,9:15 AM,12:01 PM,12:31 PM,4:57 PM

I'm sure I can do this running several queries/etc but thought maybe someone else may have a better idea?

 

[AC5FF]

Update: Got this to work as well... Three queries.
it's not perfect but it works...

Thx!

 

[The_Doc_Man]

In essence you are denormalizing data to do this. Not that you can't, but this is usually not the way we would do it. I'm going to surmise that you are doing some sort of timekeeping system where someone clocks in, works for some number of hours (which you compute as the 6th field of the original spreadsheet), and then clocks out.

You are trying to make a single-line summary of AJW's time per day, but you should take a good hard look at something else. Your sample in the first panel of this thread has a case for which THREE entries occur on 9/4/2015. How many entries can you see on one day for one Number/ECode combination? You started with a normalized spreadsheet and ended up with a denormalized sheet.

If this is what you want, you are either going to have to decide on how many of these could be on one line, or you would have to export this back to a spreadsheet on contiguous interval at a time. Access won't so easily give you a variable table.

 

[AC5FF]

Doc
I agree completely with you.

This is a time clock system I'm working with; both old and new. The data above is from a new digital time clock. Our old time clock (manual punch cards) were manually entered into a database. That DB was created by someone that knew how to work with databases, but knew nothing about normalization.

In this case - I want to find a way to import the new data into the old database so we can continue to compare reports for at least the next quarter. After that point we will likely stop using the old system.

As for the 9/4/2015 three entries... That does create a problem - but it is a once or twice a quarter occurance and will have to be manually massaged to make this work correctly. I only showed AJW data for the week I am working with; everyone else's data worked correctly when I combined things.

You think this one is bad??? HA! I'm working on another Db now that recalculates fields in an entire table when I just want to print out one record. I have nearly 20 DBs that I am constantly needing to massage data in to work correctly because none of them are normalized. But heck - it's a job and paying my bills right??

 

[gemma-the-husky]

I just tried to import the data manually, and it failed to import the time fields - so I think the way the time fields are formatted are just not correct.

not sure what the solution is, without investigating.

 

[The_Doc_Man]

Dave,

The catch with those time fields is that, left to themselves, you will always get a text field in the import destination tables. Importing from Excel-like data tests the format of the first 20 (or so) rows to see if it looks consistently like something it recognizes as a common format. However, date/time formats are almost ALWAYS "special" because the only "common" time format is the one selected for the Windows Regional settings. Just think of how many different time formats we could possible have. There is no standard unless you define one.

You MIGHT be able to import to not a new table, but to an existing table with formats already set up correctly for the job at hand. However, my own approach when seeing time strings like this would be to concatenate the date and time strings (to get a string that is now in "dd/mm/yyyy hh:nn XX" format) and then in a second query, import the concatenated string with the typecast "Date$(string)" function.

AC5FF:
As to the formatting problem, if that output format is all that you can do, then you have an impasse. You would have to decide which was more important? The start and stop times for the day or the number of hours worked for that day.

In the first case, only show the one record with start time & stop time for morning session, start and stop for afternoon session, and be done with it.

In the second case, show the one record with two times, but diddle the times when there is a third (or fourth...) time range. If the format can't be changed and fiduciary responsibility prevents diddling, you are stuck with absolutely nowhere to go.