Upload file into SQL database 2

[janeybad]

Can anyone point me in the right direction?

I would like to know the best method for inserting the data below into a SQL database.

#HEADER#
Version : 3
EOF : '^'
EOR : '~'
Property Count : 5

#DEFINITION#
AGENT_REF^ADDRESS_1^POSTCODE1^POSTCODE2^SUMMARY^DESCRIPTION^BRANCH_ID^STATUS_ID^BEDROOMS^PRICE^PROP_SUB_ID^CREATE_DATE^UPDATE_DATE^DISPLAY_ADDRESS^PUBLISHED_FLAG^LET_FURN_ID^LET_RENT_FREQUENCY^TRANS_TYPE_ID^MEDIA_IMAGE_00^MEDIA_IMAGE_01^MEDIA_IMAGE_02^MEDIA_IMAGE_03^MEDIA_IMAGE_04^MEDIA_IMAGE_05^MEDIA_IMAGE_06^MEDIA_IMAGE_07^~

#DATA#
1000001_P44OD^44 Oakley Drive^NE9^8NJ^2 Bedroom Semi-detached^2 Bedroom Semi-detached house with kitchen, en suite bathroom in master bedroom, garage and rear garden.^1000001^0^2^500^23^^^Oakley Drive, Gateshead, Newcastle Upon Tyne, NE9 8NJ^1^2^0^2^1000001_P44OD_IMG_00.jpg^1000001_P44OD_IMG_01.jpg^^^^^^^~
1000001_P76SA^76 Seafield Avenue^OX29^7BF^2 Bedroom Detached^2 Bedroom Detached, parking, garage, rear and front garden. Living room with separate dining area.^1000001^0^2^695^25^^^Seafield Avenue, Carterton, Oxfordshire^1^2^1^2^1000001_P76SA_IMG_00.jpg^1000001_P76SA_IMG_01.jpg^^^^^^^~
1000001_P190TL^190 Taptown Lane^OX28^6QP^2 Bedroom House^2 Bedroom Semi-detached house with kitchen, en suite bathroom in master bedroom, garage and rear garden. Parking also included.^1000001^0^2^750^25^^^Taptown Lane, Witney, Oxfordshire, OX28 6QP^1^2^1^2^1000001_P190TL_IMG_00.jpg^1000001_P190TL_IMG_01.jpg^^^^^^^~
1000001_P401WS^401 Wimpole Road^OX29^5SQ^1 Bedroom House^1 Bedroom Semi-detached house with kitchen, en suite bathroom, garage and rear garden.^1000001^0^1^800^25^^^401 Wimpole Road, North Street, Witney, Oxfordshire, OX29 5SQ^1^2^1^2^1000001_P401WS_IMG_00.jpg^^^^^^^^~
1000001_P101CA^22 Wellsdown Avenue^OX28^9LQ^1 Bedroom House, with parking^1 Bedroom Semi-detached house with kitchen, en suite bathroom, garage and rear garden. Parking also included.^1000001^0^1^750^25^^^Wellsdown Avenue, Witney, Oxfordshire, OX28 9LQ^1^2^1^2^1000001_P101CA_IMG_00.jpg^1000001_P101CA_IMG_01.jpg^1000001_P101CA_IMG_02.jpg^^^^^^~


#END#

Please Help

Jane XXX

 

[vacunita]

Use a Text field type or Blob type to store the big string.

You may need to pre-process the string to avoid any issues with special characters like quotes while inserting.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[janeybad]

Any chance you could give me a basic example, please.

Janey XXX

 

[vacunita]

Of what? The Insert statement is a straight forward Insert. Its just the field type in your table definition that's has the text or blob type.

As far as the Pre-processing, that will depend on what you are using to connect to your DB. For instance, PHP has a mysql_real_escape_string() that would escape the special characters prior to being used in a query.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[smays]

janeybad,

Are you wanting to dump byte-for-byte the data into one field, or are you planning on parsing out the data? What vacunita is suggesting is dumping all the data into one field. In the text of your original post, there appears to be data for at least five houses (?). If the data is currently stored in a single text file the SQL query one would generate to place this data into a database table would generate 1.0 records for this 1.0 chunks of data stored in 1.0 text files.

It appears as though this is CSV data, i.e. carat separated values (ha-ha). If the above text represents five records worth of many fields of data, you will need to parse the data first with something other SQL and then generate INSERT statements using the parsed data.


Steve.

 

[janeybad]

Dear Steve,
The above text does represent five records of many fields, so what would you suggest to parse the data? Also how would you generate INSERT statements?

Kind Regards

Jane XXX

 

[vacunita]

So you want to separate the records and insert them into a table?

From what I can gather it seems to be using the ^ character as a separator between values, and the ~ character as the terminator of a record. According to the definition section there.

The question would be is there a table in the database that has that structure? If there is then its just a question of using some type of programming language to connect to the DB, parse the string, generate the insert statements on the fly, an execute the insertions into the database.


What language do you have available to you?




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[smays]

janeybad,


As vacunita was saying, you would need to write your own code to parse out the data and then generate INSERT statements to be submitted to your database server and executed.


Now then, answer this question: is this a one time only data dump from some source to your MySQL server? Or will you be adding data like this on a regular basis? If this is a one time only deal, you could use the spreadsheet of your choice and allow it to parse your data for you. Your spreadsheet can also be used to concatenate the parsed data into INSERT statements, etc... For that matter, you could also write MACROs in your spreadsheet. The concatenated INSERT statements can be copied and pasted into an SQL file (*.SQL) to be executed by your server. One last idea (in the Winderz world), you could use Notepad.exe to replace all the '^' in the text file with ',' and use "LOAD INFILE..." to pull the data into your MySQL server's memory and place it into the appropriate fields of the specified tables.


If this was a one time deal, I would use the spreadsheet,...and, in fact, I have built and populated many of our tables in this very manner. Otherwise, since I am personally a C++ kind of guy in the Winderz world, I would write my own custom app to manage the chore of parsing the data, generating the UPDATE/INSERT queries as necessary, and updating the database.

Once every few weeks, I update calibration data for ~870 pieces of test equipment using junky, almost CSV data from some other proprietary database. Locations sometimes include "City, State"...for one location field of a CSV table. My software rejects bum data, corrects location fields, ignores unusable fields, and INSERTs/UPDATEs our equipment_serial_num table.


Steve.

 

[vacunita]

smays makes a good point, if its a one time thing, perhaps Excel may be a better alternative.

If its going to be done on a regular basis, then yes some type of application, be it in C++, Visual Basic, even PHP if you need it to be web based, would be the option.





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.