Transfer data using XML?

[evergreean43]

I am new to XML and was wondering if I could use it to take Excel 2000 spreadsheet data and transfer it to an Access 2000 database where it will take it and split it in to two tables?

Current Excel spreadsheet

City       State     Name    Age
Miami     Florida    Jones    34
Atlanta   Georgia  Smith    56
Dallas     Texas    Baker    43

Want it to look like this in Access:

TableOne
Id   City   State
1    Miami    Florida
2    Atlanta  Georgia
3    Dallas   Texas

TableTwo
Id   Name   Age
1    Jones   34
2    Smith   56
3    Baker   43

 

[Miros]

I'd have Excel export a CSV (comma separated value) file which can be easily imported into Access.

 

[Diancecht]

Can you just copy and paste to a table view in Access?

Cheers,
Dian

 

[evergreean43]

Yes but I showed the clean data.
Some of the excel data has mulitple names in the name column:

Name
Jones Rivers
Smith Baker Johnson-James

The excel spreadsheet data is not consistent and I need to transfer it to a database but it doesnt seem to be an easy transfer with the messy data in the Excel spreadsheet.

 

[Miros]

However you decide to do the export, you need a program in between to do the clean up and put some record numbers on. I'm still thinking CSV is the way to go, especially with multiple names.

Some questions to consider:

Is your data stored on a PC or a server?

What programming languages do you, or someone else in your organization know? It's going to take code of some sort to get your data "clean" enough to go into Access!

Is your data static, or is someone (or several someones) updating it on a regular basis?

If the second, is that person adding only "clean" data, or are they adding to the snarl?

Once the data is moved to a database, does that person know how to use the database instead of a spread sheet?

Will you take precautions to prevent updates to the spread sheet, but maintain an archive copy in case of disaster?

 

[evergreean43]

Is your data stored on a PC or a server?
Stored on PC

What programming languages do you, or someone else in your organization know? It's going to take code of some sort to get your data "clean" enough to go into Access!
Perl is my language of choice

Is your data static, or is someone (or several someones) updating it on a regular basis?
Excel spreadsheet will be static and it goes into database which will have a web app front end.


Will you take precautions to prevent updates to the spread sheet, but maintain an archive copy in case of disaster?
I will only get already populated data in this messy format from past entry into the Excel spreadsheet

 

[Miros]

Ok, here we go...

On thinking about this, I suspect you'll need a third file that stores the current relationships between cities and people. If you decide you don't, just skip the line that writes to file #3.

1) export your data into some sort of plain text format, such as csv or tab delimited.
2) Write a perl script to read the file. Perl's not my best language, so here's pseudocode:

citycounter = 0;
personcounter = 0;
loop:
   read line
   split into pieces by commas 
      (or tabs, however you exported it)
   [I'm assuming the city data is clean, 
      so only cleaning the person's data]
   write citycounter and city-data to file #1
   split the name field into pieces by commas.
   loop:
      write personcounter and person-data to file #2
      write citycounter and personcounter to file #3
      personcounter++ 
   endloop
   citycounter++
endloop

 

[evergreean43]

Thanks, I will give it a try.