CSV, tidying up data

[moorsey]

Heres an example of my CSV data:

71600797mS == Tue Apr 25 12:09  [172.29.100.239] 
71660381mS == Tue Apr 25 12:10  [172.29.100.239] 
71665549mS CALL:2006/04/2512:09,00:00:00,013,055623412,I,349,287349,01123456700,,,0,,""
71719967mS == Tue Apr 25 12:11  [172.29.100.239] 
71779548mS == Tue Apr 25 12:12  [172.29.100.239] 
71839131mS == Tue Apr 25 12:13  [172.29.100.239] 
71857288mS CALL:2006/04/2512:01,00:11:45,007,098415244,I,355,287355,0165124760,,,0,,""
71868853mS CALL:2006/04/2512:13,00:00:05,000,355,O,?joebloggs,,joebloggs,,,1,,""
71897725mS == Tue Apr 25 12:14  [172.29.100.239] 
71936723mS CALL:2006/04/2512:09,00:04:33,007,395,O,074655122,07725354552,joebloggs,,,0,,""
71957310mS == Tue Apr 25 12:15  [172.29.100.239] 
71992145mS ACD:5.15.1,Main,Complete,2,702,joebloggs
71992146mS CALL:2006/04/2512:03,00:12:10,007,0767676767600,I,340,287300,076656565660,,,0,,""
72016892mS == Tue Apr 25 12:16  [172.29.100.239] 
72076479mS == Tue Apr 25 12:17  [172.29.100.239] 
72114864mS CALL:2006/04/2512:17,00:00:00,015,7676767676,I,402,287402,04554656650,,,0,,""

right, ive managed to get it down to just "CALL" fields by using a query with "Not Like "*==*" And Not Like "*ACD"

but I need to further tidy this up so its all nice to view and search using a form (for a manager at work) so nice and simple.....

so for a start, how do I move bits of data from a field, i.e changing all the "71857288mS CALL:2006/04/2512:01" to seperate fields as "2006/04/25" and "12:01" or even change the order that the dat is in to "25/04/2006", can this be done using a query?

 

[Michael J Ross]

You could use the Mid Function to break up the date and time (as long as they always appear at the same place in the string). Create two calculated fields in your query one for Date and the other for time.

CallDate:Mid([fieldname], start,length)
CallTime:Mid([fieldname], start, length)

Start is the starting point in the string, and length is how many characters to include.

To rearrange the date I guess you could break up the date in into it's day, month and year parts in a query and create a calculated field that puts them back together in the order you want.

 

[moorsey]

right, I tried that, but cant get it to work, in my query, I have just typed it in a blank field to the right, replaced the values with the appropriate data "TimeOfCall" is the field it is coming from at the moment, replaced the values with 16 and 10, but keeps giving me errors, looked on the MS website, it gives a different format to the one you wrote http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctmid.asp

Im not sure if it will work for this part though, because the code at the start of the field can vary in length, is there a way to make access look for a certain character i.e. the first ":" and then keep any data after that for example?

thanks

 

[namliam]

to find the first character use Instr
To find the last character use InstrRev

Look them up in the help for more details. using stuff like mid in combination with some smart Instr stuff can save the day.
It does so for me on a near daily basis.

 

[moorsey]

thats just what i need, cheers, but can I put these functions into a query, or does it have to be coded straight into the VB side, will have a mess with it now though, thanks

access just keeps asking me to enter parameter value when i run the query.......

 

[namliam]

You can do it in VBA, but there is no real need. If it is simple like this you can do it with queries only....

 

[moorsey]

could you please explain how, I must be doing something wrong to be getting a message box every time i run the query instead of it just doing the calculation........ I wouldn't like to start dabbling in VBA

 

[namliam]

It really depends on what your trying to do....

Just as a wild example:
Myfield = "71857288mS CALL:2006/04/2512:01,00:11:45,007,098415244,I,355,287355,0165124760,,,0,,"

?mid(myfield,instr(instr(1,myfield,":")+1,myfield,":")+1,5)
01,00

As taken from the Debug window (CTRL + G)

You should be able to replace "myfield" by your actual field in the table and it should work in a query as well. Be carefull about the "" tho, I left that out in this example...

Edit: I am going to go ahead and say this, tho it seems obvious.
Assuming each comma is a field seperator, use it. Use the import manager to help you as much as possible...

 

[moorsey]

yup, its has been imported, so in the first field i am just left with "71665549mS CALL:2006/04/2512:09" and I need to get rid of "71665549mS CALL:" or just extract the date and time into seperate fields

i just seem to be having problems putting the "InStr([start, ]string1, string2[, compare])" into a query do i need a "?" infront of the whole thing

 

[namliam]

Copy these 2 lines into the query design:

TheDate: mid([YourField],Instr(1,[Yourfield],":")+ 1,10)
TheTime: mid([YourField],Instr(1,[Yourfield],":")+11,5)

Easy enough once you see it right?

The ? in the Debug window is just to produce the results in there...
?mid(myField,Instr(1,myfield,":")+ 1,10)
2006/04/25
?mid(myField,Instr(1,myfield,":")+11,5)
12:01

Hope its now clear enough

Edit:
You may want to look into functions to convert the resulting texts into real dates.....
Dateserial
Timeserial
cDate
cTime
All very usefull

 

[moorsey]

thats great yes, but, access still gives me an error, I copied it in, changed the fields appropiatly, but as before, still errors, access says:

"The expression you entered has an invalid .(dot) or ! operator or invalid parentheses"

Thanks for explaining all of this!

 

[moorsey]

its ok, I sorted it, my silly mistake, thanks for all the help, lots for me to work on now, great!!

 

[namliam]

What mistake was that then???

 

[moorsey]

I didnt realise that the calculation had to go in "field" and not "criteria", so all working well now, thanks