CODEWITHSUNDEEP
sqlms-access
nad90
nad90

Reputation: 35

Parse data from one column to multiple columns, no fixed delimitation (MS Access, SQL)

I have the following field:

: joe

: doe

: 1231231234

: [email protected]

Field(s) of Interest: Trucking   

Certifications: CDL

: Birmingham

: Michigan

Are you willing to relocate?: No

If Yes, in which states?: 

Date Available (DD214): 07/17/2015

Drivers Only: Interest in training

Upload Resume:

I would like to parse this field and insert it in multiple fields like: first name, last name, phone number, email, field of interest, certifications, city, state, date available, and drivers only.

Is this possible?

Upvotes: 0

Views: 135

Answers (2)

Benvorth
Benvorth

Reputation: 7722

Actually this is possible (at least with MySQL):

create table `tempTable` (`tempContent` VARCHAR(255));
LOAD DATA INFILE 'c:/myFile.txt' INTO TABLE `tempTable` LINES TERMINATED BY '\r\n';

delete from tempTable where `tempContent` = '';

CREATE TABLE myData (  
  first_name VARCHAR(255), 
  last_name VARCHAR(255), 
  phone_number VARCHAR(255), 
  email VARCHAR(255), 
  field_of_interest VARCHAR(255), 
  certifications VARCHAR(255), 
  city VARCHAR(255), 
  state VARCHAR(255), 
  relocate VARCHAR(255),
  relocate_states VARCHAR(255),
  date_available VARCHAR(255), 
  drivers_only VARCHAR(255)
);

INSERT INTO myData (first_name) select replace(`tempContent`, ': ', '') from `tempTable` LIMIT 0,1;
INSERT INTO myData (last_name) select  replace(`tempContent`, ': ', '') from `tempTable` LIMIT 1,1;
INSERT INTO myData (phone_number) select  replace(`tempContent`, ': ', '') from `tempTable` LIMIT 2,1;
INSERT INTO myData (email) select  replace(`tempContent`, ': ', '') from `tempTable` LIMIT 3,1;
INSERT INTO myData (field_of_interest) select replace(`tempContent`, 'Field(s) of Interest: ', '') from `tempTable` LIMIT 4,1;
INSERT INTO myData (certifications) select replace(`tempContent`, 'Certifications: ', '') from `tempTable` LIMIT 5,1;
INSERT INTO myData (city) select replace(`tempContent`, ': ', '') from `tempTable` LIMIT 6,1;
INSERT INTO myData (state) select replace(`tempContent`, ': ', '') from `tempTable` LIMIT 7,1;
INSERT INTO myData (relocate) select replace(`tempContent`, 'Are you willing to relocate?: ', '') from `tempTable` LIMIT 8,1;
INSERT INTO myData (relocate_states) select replace(`tempContent`, 'If Yes, in which states?: ', '') from `tempTable` LIMIT 9,1;
INSERT INTO myData (date_available) select replace(`tempContent`, 'Date Available (DD214): ', '') from `tempTable` LIMIT 10,1;
INSERT INTO myData (drivers_only) select replace(`tempContent`, 'Drivers Only: ', '') from `tempTable` LIMIT 11,1;

select * from myData;

Upvotes: 0

Deep Kalra
Deep Kalra

Reputation: 1428

Too long for a comment

Yes, it is possible.

We will be able to give you better insight if you tell us how the data is entering the system, for example in an Excel file.

Assuming you have a table with one column and these 13 rows. What you need to do now:

  • Add 2 columns to the table
  • Write a stored procedure to do a row by row operation to insert data in those two columns
  • First column will be hard-coded to have first name, last name, phone number, email, field of interest, certifications, city, state, date available, and drivers only
  • Second column will have a substring from ":" till the end of string

Points to remember

  1. Make sure your second new column allows null and has a suitable datatype
  2. More optimal way will be to pivot the data and store it in another table with columns like FirstName, LastName, PhoneNumber... and so on

Mention the data source and I can help you in programming it.

Upvotes: 1

Related Questions