Completado

Database conversion - SMS String to columns

I get rows of SMS data into a MySQL table and need to extract the body of the SMS into another table so my web site can parse the data.

The SMS table is called Incoming and is made up of 3 columns: ts, tel, body.

The body column is made up of a message string and is formatted as VARCHAR(255)

The body message strings contains a CSV string of data blocks and looks like this:

1242,1309161346,60,1,0,188,32,42,30,0,0,0,0,0,0,0,0,0,0,0,0,0,406,1012,322,381,738,357,206

The first block (3 or 4 digits) is always the unit identifier

The second block is always the log start date in ddmmyyhhmm format (24 hour clock used)

The third block is always the interval in minutes, i.e. 60 indicates that a data reading is every hour starting from the log start date

The fourth block is always the number of channels being used and will only be 1 digit.

The next blocks after the fourth could vary in length and the number of blocks could vary as well. These are the channel readings.

So in the example given:

unit_id = 1242

start time = 13 Sep 2016 13:46

report interval = 60 minutes

number of channels = 1

I need to move the data into another table called staging that has the following columns: id, date, flag, chan1, chan2, chan3, chan4, chan5, chan6, chan7, chan8, chan9, chan10

The flag column is defaulted to 0 and is used by the web site to mark which rows have been read into the web site.

There will never be more than 10 channels.

The date will need to be calculated from the start time plus the interval depending on the position of the data block in the message string.

The date will need to be in the default MySQL format yyyy-mm-dd

So in the example given the data should break out like this:

id date flag chan1 chan2 chan3 chan4 chan5 chan6 chan7 chan7 chan8 chan9 chan10

1242 2016-09-13 13:46 0 0

1242 2016-09-13 14:46 0 188

1242 2016-09-13 15:46 0 32

1242 2016-09-13 16:46 0 42

1242 2016-09-13 17:46 0 30

1242 2016-09-13 18:46 0 0

1242 2016-09-13 19:46 0 0

1242 2016-09-13 20:46 0 0

1242 2016-09-13 21:46 0 0

1242 2016-09-13 22:46 0 0

1242 2016-09-13 23:46 0 0

1242 2016-09-14 00:46 0 0

1242 2016-09-14 01:46 0 0

1242 2016-09-14 02:46 0 0

1242 2016-09-14 03:46 0 0

1242 2016-09-14 04:46 0 0

1242 2016-09-14 05:46 0 0

1242 2016-09-14 06:46 0 0

1242 2016-09-14 07:46 0 406

1242 2016-09-14 08:46 0 1012

1242 2016-09-14 09:46 0 322

1242 2016-09-14 10:46 0 381

1242 2016-09-14 11:46 0 738

1242 2016-09-14 12:46 0 357

1242 2016-09-14 13:46 0 206

I would like this to run as a trigger after insert on our incoming table so everything is automatic in the background.

Thank you

One additional requirement is that the procedure would not process a message string that contained alphabet. It is hoped that this would then ignore spam text messages that come into the modem.

I do not want to use PHP or CRON jobs to perform this conversion.

Habilidades: MySQL

Ver más: change database collation sms, xml database conversion, database conversion free, sms database toll free, sms enabler tutorial, sms to database software, send sms from mysql database, android sms date format, sending sms from sql server database, receive sms to database, sms database android, php, java, mysql, wordpress, database administration, database development, database programming, database conversion quark, conversion byte string

Información del empleador:
( 1 comentario ) Dundee, United Kingdom

Nº del proyecto: #15012065

Adjudicado a:

jcrbahia

Hello, I can develop your query, and through triggers, generate what you want, I would send the code of the query that would create the trigger, it would be better to work on your base, but it is not restrictive, if yo Más

£250 GBP en 10 días
(4 comentarios)
4.3

50 freelancers están ofertando el promedio de £444 para este trabajo

A2Design

Hello, We are a team of 30 developers based in Russia. We specialise in: PHP & PHP frameworks, WordPress & Woocommerce, Opencart, Magento, API development, HTML5, CSS, JavaScript, Bootstrap, MySQL, etc. Get to kno Más

£961 GBP en 9 días
(20 comentarios)
7.1
OutsourceMan

Surely, We are ready to help you with Database conversion - SMS String to columns. Can we discuss little more? We are ready to start the work now! Relevant Skills and Experience Please review our profile and past work Más

£315 GBP en 10 días
(109 comentarios)
7.2
tarekahmed

On time and On budget I can do all what you want .... I did 320 projects with a rate of 5/5 .... please check https://www.freelancer.com/u/tarekahmed.html Relevant Skills and Experience I have a Professional team for Más

£555 GBP en 10 días
(30 comentarios)
6.6
hawkscodeaus

Get rows of SMS data into a MySQL table and need to extract the body of the SMS into another table so my web site can parse the data. Relevant Skills and Experience I am good in MySQL Proposed Milestones £500 GBP - C Más

£500 GBP en 7 días
(52 comentarios)
7.0
ravichedwal

Dear sir i am fully understand your project requirement please chat with me once so i can make a good work for you Regards Relevant Skills and Experience Dear sir i have done many of CRM and Sales forces system so i Más

£555 GBP en 2 días
(37 comentarios)
6.3
lavizsolutions

Hi, Will provide you a java/C# based application to read the mysql table and parse the body data and fill into the new table. Please provide me a sample table will demonstrate you. Relevant Skills and Experience T Más

£444 GBP en 10 días
(6 comentarios)
5.6
mcfarhat

good day! i have went through your description, and i believe we can assist you creating this trigger under MySQL for mapping the data into the new table. Relevant Skills and Experience we have 13+ years of experience Más

£600 GBP en 10 días
(19 comentarios)
5.7
elbruninh

i have the job done insert rows in incoming table here [login to view URL] check [login to view URL] Relevant Skills a Más

£250 GBP en 10 días
(43 comentarios)
5.8
usatechsoft

[login to view URL] [login to view URL] [login to view URL] [login to view URL] [login to view URL] Relevant Skills and Experience I have done same work for my client ple Más

£370 GBP en 10 días
(21 comentarios)
6.0
toxsltech

Hi We are a team of 150+ highly skilled developers having huge experience in website and mobility segment (android, ios) and already executed 300+ websites, 300+ android, 250+ iphone app... Relevant Skills and Exper Más

£555 GBP en 10 días
(23 comentarios)
7.0
mahmoudgamal008

I have more than 5 years of professional and practical experience in MySQL and database development. Relevant Skills and Experience I wrote hundreds and hundreds of MySQL queries, I have more than 1800 answers in Stac Más

£500 GBP en 6 días
(29 comentarios)
5.2
sismaster

Hello sir, I can do it. BEST THAT I TRY ALWAYS PLEASE HAVE A LOOK https://www.freelancer.in/u/sismaster.html Looking for your response. Thanks, Poonam Relevant Skills and Experience HTML, CSS, Bootstrap, Wordpress Más

£250 GBP en 4 días
(51 comentarios)
5.3
prakash2813

Hi there, I have read the details, its an easy job for me. Message me, I would like to discuss over chat. Relevant Skills and Experience I have strong knowledge in plsql, procedure, trigger, dynamic sql, pivot tables, Más

£600 GBP en 10 días
(20 comentarios)
5.1
£555 GBP en 10 días
(14 comentarios)
5.3
Ajcm623

Hi, Thank you for your job posting. I can help you with my skill and experience. Relevant Skills and Experience I will do this job with regular expressiong and cron. As a linux adminstrator, I did similar works befor Más

£388 GBP en 5 días
(20 comentarios)
5.3
AxonTech

Hello, Which SMS api you are using? We can show the data into a MySQL table. Relevant Skills and Experience [login to view URL] [login to view URL] We have a team of Más

£421 GBP en 6 días
(12 comentarios)
5.4
£333 GBP en 8 días
(26 comentarios)
5.5
Shopify

Hi, I (Myself a senior PHP Developer) have a team having experience of more than 10 years. Document for this project :- - Technical Project Proposal - Flow chart for this Project - Execution plan Relevant Skills and Más

£515 GBP en 10 días
(8 comentarios)
5.2
£250 GBP en 10 días
(11 comentarios)
4.9
£777 GBP en 10 días
(4 comentarios)
4.6