Help with SQL conversion script

Any questions involving matching text strings to patterns - the pattern is called a "regular expression."

Moderator: General Moderators

User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Help with SQL conversion script

Post by Luke »

I need to be able to import a list of coordinates into a mysql database or some other readable format. The file is in the form of:

Code: Select all

1      -0.122525406135743E+03       0.416390259938272E+02
      -0.123230762000000E+03       0.420038450000000E+02
      -0.123192361000000E+03       0.420054460000000E+02
      -0.123154908000000E+03       0.420080360000000E+02
      -0.123145959000000E+03       0.420092470000000E+02
      -0.123083956000000E+03       0.420054480000000E+02
      -0.123065655000000E+03       0.420049480000000E+02
      -0.123045254000000E+03       0.420030490000000E+02
      -0.122941597000000E+03       0.420030850000000E+02
      -0.122893961000000E+03       0.420026050000000E+02
      -0.122876148000000E+03       0.420032470000000E+02
      -0.122870315059246E+03       0.420033101848239E+02
END
         2      -0.122525406135743E+03       0.416390259938272E+02
      -0.123230762000000E+03       0.420038450000000E+02
      -0.123192361000000E+03       0.420054460000000E+02
      -0.123154908000000E+03       0.420080360000000E+02
      -0.123145959000000E+03       0.420092470000000E+02
      -0.123083956000000E+03       0.420054480000000E+02
      -0.123065655000000E+03       0.420049480000000E+02
      -0.123045254000000E+03       0.420030490000000E+02
      -0.122941597000000E+03       0.420030850000000E+02
      -0.122893961000000E+03       0.420026050000000E+02
      -0.122876148000000E+03       0.420032470000000E+02
      -0.122870315059246E+03       0.420033101848239E+02
END
If anybody knows a way I could take this data (which is truncated... it's actually thousands and thousands of coordinates) and convert it to a more readable format, that would be great. Point me in the right direction.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Import the file into excel or open office, then export it as a csv, then use phpMyAdmin to import the csv.

The other option is to write a script to import it.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

astions wrote:The other option is to write a script to import it.
This was my plan.
astions wrote:Import the file into excel or open office, then export it as a csv, then use phpMyAdmin to import the csv.
I tried importing it into excel... it didn't like it very much.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

How are the lines and values separated right now, by spaces or tabs?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

I copied and pasted the way they are into the first thread of this post.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

There a few things that are going to make Excel choke on that data. One, the data is separated by spaces, not tabs. That makes each line in the file a string that Excel will want to place in a single cell. Two, if the data is straight copy and pasted, excel may choke on the negative values or it may try to truncate the exponential values to its own format.

What I would do is take that text, open it up in an editor that had a find/replace feature that will let you replace with tabs. Replace thos spacers between values with tabs. Then, copy the data, and paste it into Excel using the 'Edit' -> 'Paste Special...' feature, and choose text. Then you should have a nicely pasted rendition of the original data. Once it is in Excel, save as CSV and import into MySQL using phpMyAdmin.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I've imported data formatted like that into OpenOffice before without problems.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Look at it though... the first row has a 1 in it as the first column... the following lines (until 2) have a space there instead. If I replace all whitespace with just a tab, it will overlook the place where the empty space is supposed to be? Do you follow what I'm saying? I have already tried doing so with a very simple regex.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I'm not surprised that OpenOffice works better than Excel.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

It appears to be using a "fixed width" formating scheme. Open office can import that without problems. If you want I can have a go at it if you email it to me.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

The Ninja Space Goat wrote:Look at it though... the first row has a 1 in it as the first column... the following lines (until 2) have a space there instead. If I replace all whitespace with just a tab, it will overlook the place where the empty space is supposed to be? Do you follow what I'm saying? I have already tried doing so with a very simple regex.
Astions offered to do it for you in OpenOffice. I'll make the same offer in MS Office. Let me know in PM. If you can email it to me (or post the text on the web) I can have a got at it in Excel.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I might have this turned into a CSV in a few minutes. I'll let you know as soon as I get it.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Here ya go..
Link Removed

The first record for about 10 of the categories didn't get transferred, other than that it's good to go. The second file is in a different format and will require a different method.

Where can I send the invoice :P
Last edited by Benjamin on Thu Jul 13, 2006 6:03 pm, edited 1 time in total.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Thank you so much!!! I don't need the other file... thank you very very much... you're the man!
Post Reply