Data Clean Up.

[leungyen]

Hello,

I need help here. I need to clean up data from text file and turn it into clean database.
Basically i am stuck in how to populate blank cells with the value from prior cell.
I attached the access file to make it clear on how it looks from text file and what is the result that I am looking for.



Many Thanks!!

Sample.accdb

[rpeare]

Try saving it as a 2003 database and uploading it again, or alternately provide a sample of the original text file.

Can't open 2013 databases from my 2007 machine.

[leungyen]

Hi Rpeare, thank you for your quick reply.

Below is how the text file looks like after import it to Access

ID SalesDate Customer Sales
1
2 1/1/2015
3
4 1/1/2015
5
6 ABC 100
7
8 DEF 200
9
10 GHI 300
11
12
13 2/1/2015
14
15 2/1/2015
16
17 PQR 250
18
19 STU 150
20
21 VWX 350
22
23 XYZ 500
24
25

And below is the result that I am looking for.

SalesDate Customer Sales
1/1/2015 ABC 100
1/1/2015 DEF 200
1/1/2015 GHI 300
1/2/2015 PQR 250
1/2/2015 STU 150
1/2/2015 VWX 350
1/2/2015 XYZ 500

Many Thanks!!

[rpeare]

What does the original text file look like, importing it/linking it into access is not the best idea, parsing it with file systemobject will be more efficient than what you're trying to do.

[leungyen]

DATA.txt

Rpeare, please refer to the attached text file. What do you think is the best way to clean it and turn it into the clean database?

Many Thanks!

[rpeare]

Ok so in your file you have multiple columns of data

                        DATE  IS 01/01/15
                    ---------------- VALUE ----------------- 
                        DATE  IS 01/01/15
ID                   ABC        DEF          
7000023203             30        17        
7000024323            112       113       
7000024847           (339)      186        
7000024960         (1,502)      123          
7000025279            490       215        
7000026193             15         4        
7000027073          3,279       923        
7000027391            371       140

I've taken out the extra blank lines for the sake of being compact.

You have two dates in your test file and each day has a column for, what I assume is a vendor, is this consistent so that if you had customer GHI and JKL you would have something like this:

                        DATE  IS 01/01/15
                    ---------------- VALUE ----------------- 
                        DATE  IS 01/01/15
ID                   ABC        DEF        GHI        JKL
7000023203             30        17         10         11        
7000024323            112       113         20         22       
7000024847           (339)      186         30         33        
7000024960         (1,502)      123          
7000025279            490       215        
7000026193             15         4        
7000027073          3,279       923        
7000027391            371       140

[leungyen]

You are right. The layout of the text file is always consistent.

[June7]

You can open the text file in Notepad or Excel and do a lot of copy/cut and paste to arrange the data to normalized structure or write a lot of code to do the specialized import.

http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

Bing: access vba read write text file

http://www.tek-tips.com/faqs.cfm?fid=3767

[leungyen]

You can open the text file in Notepad or Excel and do a lot of copy/cut and paste to arrange the data to normalized structure or write a lot of code to do the specialized import.

http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

Bing: access vba read write text file

http://www.tek-tips.com/faqs.cfm?fid=3767

Actually I prefer to do it from Access itself with formula either in Expression Builder or Sql from Access. I hope there is a simple way to do it.
How to populate the date to the next row. In Excel it is very simple eg. From the cell #A2 I can type if(B2="",A1,B2).
The data that i am working on now will have hundred thousand of rows daily, I need to work from Access since Excel cannot handle it.
Maybe other advance Access community can help?

Thanks so much!!

[June7]

Already suggested methods for import of data to normalized structure.

Making a record refer to value from another record requires nested subquery or domain aggregate function.

Review http://allenbrowne.com/subquery-01.html#AnotherRecord

[leungyen]

I can't get the results that I want. Thanks anyway.

[ssanfu]

Actually I prefer to do it from Access itself with formula either in Expression Builder or Sql from Access. I hope there is a simple way to do it.
How to populate the date to the next row. In Excel it is very simple eg. From the cell #A2 I can type if(B2="",A1,B2).

The first thing to do is forget everything you know about Excel. I'm very serious - I am speaking from experience. Access is not Excel.
Even though a table or a form in datasheet view LOOKS like a spreadsheet, it is not. Access does not have "cells". You cannot reference anything using "A1".
Access tables have fields and records.

As to importing/parsing, it is not hard, just writing a lot of VBA code.
1) Select the file using a file picker (or you can loop through every file in a folder)
2) Open the Text/CSV file
3) Read in enough lines to get to the first data line. In your example Data file, the first line would be line 4, the date.
4) Read lines to get to the line with the company names. (Note: the text file structure must be the same every time or there must be a lot more code.)
5) Parse the line to get the company names
6) Read lines until the data for the companies is reached
7) Parse the data
8) Insert new records into the recordset (table). In your case there would be two records, one for each company.
9) Loop until the end of the text file is reached.
10) Do clean up tasks.

This is just a basic outline of the process. There will be validation code necessary and there should be error handling code.

I receive a CSV (text) file that has 23 columns and approx.12,000 lines to import. The data is written to 6 tables. Lots of validation is necessary to ensure clean data and no data duplication of employee data.
A second CSV file has 7 columns and up to 2,000 rows. This data has lots of calculations performed for each line before it is written to a table.


I do still program in Excel, but it takes an effort to remember how to do things. I have to forget about Access programming. And sometimes it takes longer because I mix up Access and Excel functions and methods. But eventually everything works out.

[rpeare]

There is no way that I can think of to do this without using vba, queries would just be monstrous to make it happen, I will try to fix up some code for you on monday as an example to follow.

[rpeare]

I've just looked at your sample database and the data arrangement in there does not match your text file.

Can you provide an actual sample of the original data that has all the elements you want to extract in the same arrangement. For instance your sample data does not include the ID field, nor does it have multiple columns (1 for each customer).

Do not create a text file, take an actual text file you receive and change the data in a portion of it and save it as a new document because you're giving really mixed up sets of data.

[June7]

Also confused. Data.txt file is not same structure as TextFile table.

What method are you currently using to import?

Since the textfile structure is not suitable for a link, will require VBA code that opens and manipulates text file object.