0

I am setting up a new database and I want to import lots of data from multiple spreadsheet files. One of the fields in these documents contains an array of data, each item declared on seperate rows as follows:

Field 1 ¦ Field 2 ¦ Field 3 ¦ Field 4 ¦ Field 5 ¦
=================================================
 value  ¦  value  ¦  value  ¦  value  ¦  value  ¦
-------------------------------------------------
        ¦         ¦         ¦  value  ¦         ¦
-------------------------------------------------
        ¦         ¦         ¦  value  ¦         ¦
-------------------------------------------------
        ¦         ¦         ¦  value  ¦         ¦
-------------------------------------------------
 value  ¦  value  ¦  value  ¦  value  ¦  value  ¦
-------------------------------------------------
        ¦         ¦         ¦  value  ¦         ¦
-------------------------------------------------
        ¦         ¦         ¦  value  ¦         ¦
-------------------------------------------------

EDIT: My CSV files look like this (same as above):

value,value,value,value,value
,,,value,
,,,value,
,,,value,
value,value,value,value,value
,,,value,
,,,value,

What I'm looking to do is end up with something like the following (collecting all values from the 4th field together):

value,value,value,{value,value,value},value
value,value,value,{value,value},value

At that point then I would be importing it into a database. I have tonnes of this data so its not possible to manually edit it.

How best to get from A to B? Is there any script out there to help?

NOTE: The 4th field doesn't necessarily need to be an array, could be just text separated by line brakes

closed as unclear what you're asking by Paul White Feb 19 '16 at 1:58

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Which of Field1,Field2,Field3, and Field5 are needed to uniquely identify each master entity? All 4 of them or a subset? – Pieter Geerkens Jul 27 '14 at 18:59
  • Can you give us more context. In what kind of database do you import? What is the definition of your tables? Why don't you normalize your data? Or is this only a string processing topic: how can I transform a textfile to another textfile with another structure? YOu made a mistake, Your result records should be "value,value,value,{value,value,value,value},value" and "value,value,value,{value,value,value},value" – miracle173 Jul 27 '14 at 22:44
  • Which database product are you using? Please tag the question accordingly e.g. [mysql]. Include a version tag as well. Placing this question on hold while you clarify it. Please respond to comments by editing your question body to include the requested details. Unclear questions generate guess-my-problem answers, which just wastes time (e.g. jynus' answer below). – Paul White Feb 19 '16 at 1:56
1

Normalize. Determine which of fields 1-5 represent they, and pre-process your CSV into two files representing two related tables that can be joined on that key.

For example, if fields 1 and 2 uniquely identify each row, then you need one table with field 1,2,3,5 and another with 1,2,4. Save yourself some later trouble by defining primary keys for both tables.

awk(1) will split the file nicely, but beware: because the definition of a CSV file is not a regular language, it can't be parsed (in its full generality) with regular expressions. It would be preferable to export the data as e.g. tab-delimited.

  • Ok, well a better question would be what method to use for pre-processing the CSV? I know I would have to split into two files with an extra field to match them up. I have more experience with jQuery and was going to give a stab at that but thought there would be far better methods. I've read some examples with php, but their examples were confusing. – Patrick Keane Jul 30 '14 at 19:06
0

This ugly sed expression seems to do the job:

$ sed -e '/^\=*$/d; /^\-*$/d; s/¦$//g; s/^ *//g; s/ *¦ */,/g' > /tmp/import
Field 1 ¦ Field 2 ¦ Field 3 ¦ Field 4 ¦ Field 5 ¦
=================================================
 value  ¦  value  ¦  value  ¦  value  ¦  value  ¦
-------------------------------------------------
        ¦         ¦         ¦  value  ¦         ¦
-------------------------------------------------
        ¦         ¦         ¦  value  ¦         ¦
-------------------------------------------------
        ¦         ¦         ¦  value  ¦         ¦
-------------------------------------------------
 value  ¦  value  ¦  value  ¦  value  ¦  value  ¦
-------------------------------------------------
        ¦         ¦         ¦  value  ¦         ¦
-------------------------------------------------
        ¦         ¦         ¦  value  ¦         ¦
-------------------------------------------------
        ¦         ¦         ¦  value  ¦         ¦

$ mysqlimport -u root --ignore-lines=1 --fields-terminated-by=, test /tmp/import
test.import: Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

$ mysql -e "SELECT * FROM test.import"
+--------+--------+--------+--------+---------+
| field1 | field2 | field3 | field4 | field5  |
+--------+--------+--------+--------+---------+
| value  | value  | value  | value  | value   |
|        |        |        | value  |         |
|        |        |        | value  |         |
|        |        |        | value  |         |
| value  | value  | value  | value  | value   |
|        |        |        | value  |         |
|        |        |        | value  |         |
|        |        |        | value  |         |
+--------+--------+--------+--------+---------+
  • Sorry, I wasn't referring to the characters --- ¦. This data is actually interpretated from a CSV file. Basically, there should only be 2 records, and the 4th field contains an array of data. See my edit – Patrick Keane Jul 27 '14 at 18:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.