General discussion

Locked

Import Data Records - Transpose Format?

By kdrungilas ·
I receive data from an e-mail source and from downloads that I want to import into a database. I'm trying Access, but could also use Filemaker or even SQL and am open to other recommendations. Here's the issue:

The data is received as a list with about 15 field names on the left and record data on right. The second record is below the first--with both field names and data. The third is below this , and so on.

I can bring data into Excel, use the copy, paste special...transpose in order to get my field names at the top and a record below horizontally, but I don't want to do this with 700 records at a time. Any data conversion utilities/code out there that will do this for me?

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Import Data Records - Transpose Format?

by Peyison In reply to Import Data Records - Tra ...

Are all of the field names the same? If you can post the file layout, or better yet, send a sample file, I can either post Excel or Access VBA code to create a new file, or send a VB exe to do it, or post VBA code to import the file into Access witout saving the data to a new file.

My email is mike@peyison.com

Collapse -

Import Data Records - Transpose Format?

by kdrungilas In reply to Import Data Records - Tra ...

Poster rated this answer

Collapse -

Import Data Records - Transpose Format?

by kdrungilas In reply to Import Data Records - Tra ...

Field names are all the same. Two-record sample looks like:
<normal>
<curr_dt> 3/1/01
<last_dt> 12/4/00
<last_update_dt> 2/28/01
<dr_num> 93- 688770
<dr_ver> 20
<dr_type> NOR
<title_name> St Louis Marriott Renaissance Convention Hotel (New/Renov)

<normal>
<curr_dt> 12/20/00
<last_dt> 8/3/00
<last_update_dt> 12/20/00
<first_dt> 1/23/92
<dr_num> 91- 325914
<dr_ver> 22
<dr_type> NOR
<title_name> Renner Business Center Office/Warehouse Complex

Collapse -

Import Data Records - Transpose Format?

by donq In reply to Import Data Records - Tra ...

If (big if) your data always arives with the same Format AND File Name you have it made! Leave the incoming data on disk and write an Access Update and/or Append query that places everything directly into Access. I think that is what Mike has in mind to do for you?

Collapse -

Import Data Records - Transpose Format?

by kdrungilas In reply to Import Data Records - Tra ...

Thanks, but you didn't explain how I can write an Access update query.

Collapse -

Import Data Records - Transpose Format?

by Raffi_ In reply to Import Data Records - Tra ...

A simple Word macro can help you out here. The macro would turn your first (and any subsequest) record into this:

"","3/1/01","12/4/00","2/28/01 ","93- 688770","20","NOR","St Louis Marriott Renaissance Convention Hotel (New/Renov)"

This is then easily parsed into any access table. The table can be pre-built or you can add the field names as the first row of the import file.

To build the macro do a search and replace for each ^p (carriage return) field_name and replace it with a ",". You then do a search for ^p^p and replace it with ^p thus getting rid of the unwanted lines between records.

The macro, for 700 records, should take 0 seconds on a reasonably fast machine. Then just use the import facility in Access to convert theCSV (CommaSeperatedValue) into a table.

What you have to watch out for is that each record occupies only 1 line. If not then you have to make sure that the end of your record is more clearly marked. So the search for the first filed would insert a special "end of record" marker for the preceeding record.

I have used this technique for years even on very large files (> 100,000 records)

Regards
Raffi_

Collapse -

Import Data Records - Transpose Format?

by kdrungilas In reply to Import Data Records - Tra ...

Poster rated this answer

Collapse -

Import Data Records - Transpose Format?

by kdrungilas In reply to Import Data Records - Tra ...

This question was closed by the author

Back to Web Development Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums