Can Access Expression Builder mirroring "IF function" from Excel

liozzi

Registered User.
Local time
Today, 15:55
Joined
Mar 29, 2013
Messages
14
Hello,

I need to clean up data from text file which is huge.
I wonder if Access Expression Builder can mirroring "IF function" from Excel
Here is what i am trying to do
The data consist of multiple customer and multiple date. But the layout only specified customer ID once eg.
CustID : aaaa
Date
01012013
02012013
03012013
CustID : bbbb
01012013
02012013

When exported the file I used Fixed Width command to separate Date Column and Cust ID column.

Below is the result that I am looking for
CustID : aaaa : aaaa
Date : aaaa
01012013 : aaaa
02012013 : aaaa
03012013 : aaaa
CustID : bbbb : bbbb
01012013 : bbbb
02012013 : bbbb

1. Column A is Date
2. Column B is Customer ID
because Customer ID in column B only appear once, I need to create another column to populate that Customer ID whenever the transaction related with that Customer
3. Column C is the column where i tried to populate Customer ID to each date related to that Customer.

If I worked in excel the formula will be --> IF(AND(C5="",B6=""),"",IF(AND(C5="",B6<>""),B6,IF(B6<>"",B6,C5)))but in access i am stucked.

Please help and Thanks in advance!!
 
Where is this data going in the end? I ask because Access is SUPREMELY unsuited to what you are trying to do... unless you change the way you think about your data. Access doesn't want columns of data, it wants rows. If you're just going to spit this back into another text file, it may not be worth messing with Access at all.

If it's going to be dumped into an Access database in the end for some other purpose, then we should talk about how to normalize your data structures.
 
I need to clean up the data from text file. The cleaned data will be use as the raw data for my other analysis.
:banghead:
 
In a relational database? Because that affects the answer.
 
Yes. Relational database.
Appreciate your help!!
 
Okay, good.

Is the text file a CSV? I noticed you said you had to use Text-to-Columns to get your data to work in Excel... what does a RAW line from the file look like? Strip any sensitive information, but replace it with dummy values please. Also is CustID numeric, alpha, alphanumeric, etc? Are dates always formatted mmddyyyy exactly? I'm guessing yes.

If it really looks like "CustID : aaaa" I think we're in business... are there other values/columns besides these two? Oh and how many lines are we dealing with here? And is this a one-time conversion, or something you'll have to do every time you want to analyze the data?
 
No, the text file is not a csv file.

CustomerID is numeric. Dates always formated as mm/dd/yy

There's some other columns other than date and customer ID, such as let's say price and quantity.
We're dealing with approximately more than 1 million rows. that's why I can't use excel.
This analysis will be for one time with a possibility to going forward.

I attached the access file for your reference for what i've done so far. It's not graceful, any comment on how to improve it will be highly aprreciated.

Query 4 is pretty much what i am looking for but instead of date I need it to return CustID
I did try i on Query 5 but it seems not successful.

Thanks David!!
 

Attachments

Boy, that is one UGLY file, you're right. :D Were you able to load all million+ rows into Access without hitting the filesize limit?

While I'm stalling, I mean looking at it, what do you want it to look like in the end? Do you need all the location data too, or just CustID and OrderDate?
 
Also are the first seven rows of each customer record ALWAYS exactly the same format? CustID, CustName, blank, blank, Headers, blank, [DATA]?
 
I'm thinking of if I could have customer id and date in the same row, it would be easier for me to tweak it.
So to summarize it I want the date and customer ID in the same row. Do you think it's possible? otherwise might have to cut the file in the smaller smaller sizes (which i avoided) and do the work from excel. It would be very painful :(

The problem that make it hard to make it is the text file only have customer ID stated once and all the transaction after it, and it continues to other customer.

And yes the first seven rows of each customer record ALWAYS exactly the same format. It is pretty consistent.

Thanks David!!!

Not rush until Monday though, Have a nice weekend!!!
 
I think I can think (heh) of a way to step through that whole long file and drop CustID into a blank field between ID and Date (don't name fields just Date by the way... I assume your real table is different, but that's a keyword in Access).

I'll try to draft something up on Monday, though someone else may have a better solution... my way tends to be a bit brute-force. I hope you have plenty of RAM! :D
 
That's sound awesome.
Yes, I didn't name it just 'date'. Thanks for point it out.
And yes, don't worry about RAM.
Any help is appreciated. I think for my typical data file that i have it needs brute-force approach. :)
Thanks again!!
 
I have not forgotten you, but work has exploded this week. If someone else can figure this out I welcome them to take on the project.

Essentially what I'm thinking is (and this is for other VBA coders, not you lliozzi unless you know a lot more VBA than you're letting on :p) to add a new column, and then step through the WHOLE recordset.
  1. Set A=1
  2. If rowA is Not Null and Numeric and rowA+1 is Not Null, store rowA+1's contents in a variable.
  3. Verify that RowA+4 is "TRANSACTION"; if not, exit with an error message.
  4. If we're okay, any row A+6 on up until A+n is blank, paste our stored CustID into NewColRowA+6.
  5. Once A+n is blank, blank the stored CustID, reset A to 2 rows more, and start from the top.
There's probably an easier way to do it, but I've been working in C code this week....
 
OK! I never promised you pretty code, but this seems functional.. obviously work on a COPY of your data first (you have the text file to reload, but stilll...)

Code:
Option Compare Database
Option Explicit

Public Function BruteForce(Optional a As Long)

Dim rs As DAO.Recordset
Dim CustID As String

    Set rs = CurrentDb.OpenRecordset("SELECT ID, Customer, TransDate, CustID FROM TextFile ORDER BY TextFile.ID", dbOpenDynaset) 'try to shrink the recordset size a LITTLE bit!
    
    rs.MoveFirst
    If rs.RecordCount = 0 Then GoTo whoops 'not likely to be the problem here, but just in case
    
    If a = 0 Then
        a = 1 'begin at the beginning
        If rs.EOF Then GoTo notwhoops 'the end of the file
    Else
        rs.FindNext "ID = " & a 'begin where we left off, if necessary
        If rs.NoMatch Then GoTo whoops 'we seem to have given a bad number??
    End If
    
    If a > 1 Then
        rs.MovePrevious 'let's test something...
        rs.FindNext "[TransDate] = 'CUSTOMER ID'" 'take advantage of our weird formatting - this SHOULD be where we started anyway, but just in case we were off by one...
        If rs.NoMatch Then GoTo notwhoops 'we appear to be at the end of our usefulness
    End If
    
storedata:
    CustID = rs!CustID 'store the badly formatted version somewhere we can access at will.
    Debug.Print CustID 'let's give the user something to look at, below...
    
    rs.MoveNext '"CUSTOMER NAME" row
    If rs.EOF Then GoTo notwhoops 'the end of the file
    rs.MoveNext 'first blank row
    If rs.EOF Then GoTo notwhoops 'the end of the file
    rs.MoveNext 'second blank row
    If rs.EOF Then GoTo notwhoops 'the end of the file
    rs.MoveNext '"DATE/TRANSACTION/LOCATION" row
    If rs.EOF Then GoTo notwhoops 'the end of the file
    If rs!CustID <> "TRANSACTION" Then GoTo notwhoops 'something is horribly wrong, abort!
    rs.MoveNext 'next blank row
    If rs.EOF Then GoTo notwhoops 'the end of the file
    rs.MoveNext 'first row of real DATA!
    If rs.EOF Then GoTo notwhoops 'the end of the file
    
pastedata:
    Do Until IsNull(rs!TransDate) 'loop through the non-blank rows, adding the stored CustID each time
        rs.Edit
        If IsNull(rs!Customer) Then rs!Customer = CustID
        rs.Update
        a = rs!ID
        rs.MoveNext
        If rs.EOF Then GoTo notwhoops 'end of file here!
    Loop
    
    
    rs.FindNext "[TransDate] = 'CUSTOMER ID'" 'find the next one - it SHOULD be three rows down, but let's not count on it
    If rs.NoMatch Then GoTo notwhoops 'we seem to be at the end of the file
    If rs.EOF Then GoTo notwhoops 'the end of the file
    
    GoTo storedata 'go back and do it again!

whoops:
    MsgBox "I don't know what happened, but we lost our place about row " & a & "..."
    rs.Close
    Set rs = Nothing
    Exit Function
    
notwhoops:
    MsgBox "We appear to be finished about row " & a & "... to the bar!"
    rs.Close
    Set rs = Nothing
    Exit Function
End Function
Load that into a new module, save it, then in the VBA Immediate Window type
Code:
call bruteforce
and watch your computer go... I hope! If it gets stuck or you need to restart it partway through, you can call bruteforce(line#) to make it jump past the first 50k records, or whatever... good luck!

Then you should just need to turn your Query2 into a lookup table for Customers/CustIDs, if you don't have that already, and make Query6 into your new table:
Code:
SELECT TextFile.*
FROM TextFile
WHERE (((TextFile.Customer) Is Not Null));
And THEN you can start normalizing this monstrosity!
 
Last edited:
Ups sorry just saw your reply :eek: Can't thank you more :)

That's pretty intimidating. Thanks again for your help. I've got to figure it out how to make it work based on your instruction. I'll post the update.

Thanks again!!!
 
Post back if you need help, but it worked on a copy of your data for me, even when things weren't exactly spaced always exactly the same...
 
Will do. I'm about to try it now.

Thanks again David!!:)
 
Hi David,

I am stuck with the code.
Please find the attached pdf.
I can't make it work. Could you please give me a feed back?

Thanks and have a good weekend !! :)
 

Attachments

I'm a bit of a latecomer, but this query produces what I think you want

Code:
SELECT (SELECT first(CustID) FROM [Text File] as tmp where ID=T.tmpID) AS CID, (Select first(custid) from [text file] as tmp where id=t.tmpid+1) AS CName, T.Date, T.custid AS [Transaction], T.Location, T.x, T.y, T.z, T.xy, T.za
FROM (SELECT [Text File].*, Nz((Select max(id) from [text file] as tmp where ID<[text file].id and Date="Customer ID"),[ID]) AS TmpID
FROM [Text File])  AS T
WHERE (((T.Date) Not In ("Date","Customer ID","Customer Name","")))
You just need to copy and paste it into the sample db you posted earlier.

See attachment for the result
 

Users who are viewing this thread

Back
Top Bottom