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)
0.0

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

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)
0.0
srsinfosystems

Yes, I can surely help you out with your project. I have few questions regarding the project and would like to discuss them with you as per your convenience. Relevant Skills and Experience PHP/OOP, CodeIgniter,CakePHP Más

£466 GBP en 12 días
(4 comentarios)
0.0
zdesign77

Hey, how’s it going? My name is Mike, I’m a Web Designer & Developer from Boston. I've had a look at your project description and feel that my skills match your requirements perfectly. Relevant Skills and Experience G Más

£555 GBP en 10 días
(0 comentarios)
0.0
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
(49 comentarios)
0.0
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)
0.0
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
(40 comentarios)
0.0
huongth

Hi. I can create auto scripts to scrape websites, auto click, format txt, csv, xls, xlsx, doc, docx, rtf, json, xml, database files as you request. I can start right now Relevant Skills and Experience I am an expert i Más

£277 GBP en 3 días
(0 comentarios)
0.0
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
(46 comentarios)
0.0
kazimking

Hello Sir, hope you are fine, I am interested in your work "SMS String to columns". I will create store procedure for given task ? Relevant Skills and Experience I have 5 year experience in Web Development Service. Más

£400 GBP en 10 días
(18 comentarios)
0.0
freelancerkpis

We are happy to bid on this project. We provides dedicate developers and Development and design services as well. ***** We are having a great team of Mobile developers with 7-8 years of experience Relevant Skills a Más

£555 GBP en 10 días
(1 comentario)
0.0
djcarras

hi, i can help you with your project. send me more information about via freelancer.com chat. regards Habilidades y experiencia relevante linux php mysql mariadb sql server Htos propuestos £25 GBP - freelancer.com fe Más

£250 GBP en 1 día
(5 comentarios)
0.0
BeshoyMousaKhair

. Relevant Skills and Experience . Proposed Milestones £400 GBP - .

£400 GBP en 10 días
(0 comentarios)
0.0
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
(105 comentarios)
0.0
£555 GBP en 10 días
(0 comentarios)
0.0
nikhilmisal

Hi, I can help you in this script. Need some clarification. How the data will look in SMS table in case if no of channels is more than 1? I can provide trigger with my current understanding so that you can check it's Más

£300 GBP en 3 días
(0 comentarios)
0.0
£555 GBP en 10 días
(0 comentarios)
0.0
ManishaS06

Hi, As per my understanding your requirement is to create one trigger, Run it on any new row created in the SMS table to insert one row with specified columns to the staging table. Regards. Relevant Skills and Expe Más

£255 GBP en 5 días
(0 comentarios)
0.0
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)
0.0
abruck

So, in the example you give; 1242 2016-09-13 13:46 0 0 there is only one channel. That row coming from 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 Relevant Skills Más

£750 GBP en 10 días
(0 comentarios)
0.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)
0.0