Page 1 of 2

Help with SQL conversion script

Posted: Wed Jul 12, 2006 12:26 pm
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.

Posted: Wed Jul 12, 2006 2:26 pm
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.

Posted: Wed Jul 12, 2006 4:23 pm
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.

Posted: Wed Jul 12, 2006 4:24 pm
by RobertGonzalez
How are the lines and values separated right now, by spaces or tabs?

Posted: Wed Jul 12, 2006 4:59 pm
by Luke
I copied and pasted the way they are into the first thread of this post.

Posted: Wed Jul 12, 2006 5:13 pm
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.

Posted: Wed Jul 12, 2006 5:18 pm
by Benjamin
I've imported data formatted like that into OpenOffice before without problems.

Posted: Wed Jul 12, 2006 5:19 pm
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.

Posted: Wed Jul 12, 2006 5:19 pm
by RobertGonzalez
I'm not surprised that OpenOffice works better than Excel.

Posted: Wed Jul 12, 2006 5:24 pm
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.

Posted: Wed Jul 12, 2006 5:25 pm
by Luke

Posted: Wed Jul 12, 2006 5:28 pm
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.

Posted: Wed Jul 12, 2006 6:01 pm
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.

Posted: Wed Jul 12, 2006 6:05 pm
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

Posted: Wed Jul 12, 2006 6:10 pm
by Luke
Thank you so much!!! I don't need the other file... thank you very very much... you're the man!