Here's what I did when I have CSV files that I DIDN'T know what
positions the header columns were in. My issue was that the sensors in the
probes that supply the data could be in different orders to other handsets.
I knew the first 5 column headers were constant but the other 5 could be in
any order thus creating a nightmare when importing the CSV data into the
system. I have attached the snippet of code on how I resolved the issue
just incase your faced with a similar problem
1. The CSV file always had the column header names on line 6 (one saving
grace)
2. Gateway creates a table to get the data into the system
3. A series of variables looks up what the column header values in each
column
4. Alter table commands rearrange the table accordingly.
Once the initial table has the properly named headers I can move the data
into the main system in the correct order.
On Wed, Jun 30, 2021 at 10:03 AM <[email protected]> wrote:
> Thanks R:azzak,
>
> Yes, it helps. I was using the option to create a new table from the
> import file and the "Specifications" button is not available; It is only
> available when importing data into an existing table. I have used this
> approach before and I knew there was a way of generating a specification
> file.
> I can create the receiving table ahead of time as a workaround. I am
> trying to create a utility that automates the entire process, from
> importing the data, adding an index adding calculated columns and then
> running a procedure to populate another table with the averaged values.
> I should have enough now to get me going. 😊
>
> Javier,
>
> Javier Valencia, PE
> 913-915-3137
>
>
> -----Original Message-----
> From: [email protected] <[email protected]> On Behalf Of A.
> Razzak Memon
> Sent: Tuesday, June 29, 2021 5:03 PM
> To: [email protected]
> Subject: Re: [RBASE-L] - Importing Data
>
> Javier,
>
> It is the [Specifications ...] button during the whole process of setting
> up the Import process.
>
> See attached.
>
> Hope it helps!
>
> Very Best R:egards,
>
> Razzak
>
> At 05:47 PM 6/29/2021, [email protected] wrote:
>
> >I need to import 170,000 rows of data from either a CSV or an Excel
> >file and I would like to create a specification file  (*.RGW) with all
> >the parameters so I can run t from command line. I remember having the
> >Â option to create the import using the Gateway Utility and then
> >storing the parameters in a RGW type file but I do not seem to find
> >where the option is or how to do it.
> >
> >When importing using the Excel import it gives me the option of using
> >row 1 to read the column names; I do not seem to get this option when
> >using CSV; is this not available?
> >
> >Also, when changing the columns names on the import file to match
> >current columns, it tells me the columns already exist even when the
> >type and length are identical so I have to change the name to something
> else.
> >
> >Obviously I have not used this feature in a while and my skills are
> >rusty and the newer version are probably different than the last one
> >when I use Gateway. Any light you can shed would be appreciated.
> >
> >Javier,
> >
> >Javier Valencia, PE
>
> --
> For group guidelines, visit
> http://www.rbase.com/support/usersgroup_guidelines.php
> ---
> You received this message because you are subscribed to the Google Groups
> "RBASE-L" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/rbase-l/0LptxZ-1lKsfD37El-00fi3C%40mrelay.perfora.net
> .
>
> --
> For group guidelines, visit
> http://www.rbase.com/support/usersgroup_guidelines.php
> ---
> You received this message because you are subscribed to the Google Groups
> "RBASE-L" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAG1gU%2BA71stIig71BAFnx9iihQAAEAAAAA66%2Bbnmq3pGoOuGF2JPWzkBAAAAAA%3D%3D%40vtgonline.com
> .
>
--
Regards
Tony
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/CABnhDARC-NycvkVWdgve36ahLyzqq%3DK2Tqv%2BmOfHtG%2B4KSxVOQ%40mail.gmail.com.
--Import the csv file
DROP TABLE kordss
SET VAR vfile = (.vdir+'\dataimport\'+.vfile)
GATEWAY IMPORT CSV &vfile CREATE kordss OPTION FIRST_ROW 6|SHOW_PROGRESS ON
-- Dynamic table creator
SET VAR vnewguid TEXT = NULL
-- Hold the constant header values
SET VAR vcol1 TEXT = 'Rundate'
SET VAR vcol2 TEXT = 'Runclock'
SET VAR vcol3 TEXT = 'Site'
SET VAR vcol4 TEXT = 'Dataid'
SET VAR vcol5 TEXT = 'Temperature'
-- Hold the variable header titles
SET VAR vcol6 TEXT = NULL
SET VAR vcol7 TEXT = NULL
SET VAR vcol8 TEXT = NULL
SET VAR vcol9 TEXT = NULL
SET VAR vcol10 TEXT = NULL
-- Get the names of the headers from the import file that are variable
SET VAR vcol6 = field6, vcol7 = field7, vcol8 = field8, vcol9 = field9,
vcol10 = field10 FROM kordss WHERE field1 = 'date'
IF vcol10 IS NULL THEN
SET VAR vcol6 = field6, vcol7 = field7, vcol8 = field8, vcol9 = field9 FROM
kordss WHERE field1 = 'date'
ENDIF
-- Based on the vcol variable alter the handimport Kordss table
-- Looking for whats in column 6
IF vcol6 = 'Sal (ppt)' THEN
ALTER TABLE kordss ALTER COLUMN field6 TO salppt REAL
ENDIF
IF vcol6 = 'turbidity (ntu)' THEN
ALTER TABLE kordss ALTER COLUMN field6 TO turbidity REAL
ENDIF
IF vcol6 = 'odo (% sat)' THEN
ALTER TABLE kordss ALTER COLUMN field6 TO odosat REAL
ENDIF
IF vcol6 = 'odo (mg/l)' THEN
ALTER TABLE kordss ALTER COLUMN field6 TO odo REAL
ENDIF
IF vcol6 = 'pH' THEN
ALTER TABLE kordss ALTER COLUMN field6 TO phlevel REAL
ENDIF
-- Looking for whats in column 7
IF vcol7 = 'Sal (ppt)' THEN
ALTER TABLE kordss ALTER COLUMN field7 TO salppt REAL
ENDIF
IF vcol7 = 'turbidity (ntu)' THEN
ALTER TABLE kordss ALTER COLUMN field7 TO turbidity REAL
ENDIF
IF vcol7 = 'odo (% sat)' THEN
ALTER TABLE kordss ALTER COLUMN field7 TO odosat REAL
ENDIF
IF vcol7 = 'odo (mg/l)' THEN
ALTER TABLE kordss ALTER COLUMN field7 TO odo REAL
ENDIF
IF vcol7 = 'pH' THEN
ALTER TABLE kordss ALTER COLUMN field7 TO phlevel REAL
ENDIF
-- Looking for whats in column 8
IF vcol8 = 'Sal (ppt)' THEN
ALTER TABLE kordss ALTER COLUMN field8 TO salppt REAL
ENDIF
IF vcol8 = 'turbidity (ntu)' THEN
ALTER TABLE kordss ALTER COLUMN field8 TO turbidity REAL
ENDIF
IF vcol8 = 'odo (% sat)' THEN
ALTER TABLE kordss ALTER COLUMN field8 TO odosat REAL
ENDIF
IF vcol8 = 'odo (mg/l)' THEN
ALTER TABLE kordss ALTER COLUMN field8 TO odo REAL
ENDIF
IF vcol8 = 'pH' THEN
ALTER TABLE kordss ALTER COLUMN field8 TO phlevel REAL
ENDIF
-- Looking for whats in column 9
IF vcol9 = 'Sal (ppt)' THEN
ALTER TABLE kordss ALTER COLUMN field9 TO salppt REAL
ENDIF
IF vcol9 = 'turbidity (ntu)' THEN
ALTER TABLE kordss ALTER COLUMN field9 TO turbidity REAL
ENDIF
IF vcol9 = 'odo (% sat)' THEN
ALTER TABLE kordss ALTER COLUMN field9 TO odosat REAL
ENDIF
IF vcol9 = 'odo (mg/l)' THEN
ALTER TABLE kordss ALTER COLUMN field9 TO odo REAL
ENDIF
IF vcol9 = 'pH' THEN
ALTER TABLE kordss ALTER COLUMN field9 TO phlevel REAL
ENDIF
-- Looking for whats in column 10
IF vcol10 = 'Sal (ppt)' THEN
ALTER TABLE kordss ALTER COLUMN field10 TO salppt REAL
ENDIF
IF vcol10 = 'turbidity (ntu)' THEN
ALTER TABLE kordss ALTER COLUMN field10 TO turbidity REAL
ENDIF
IF vcol10 = 'odo (% sat)' THEN
ALTER TABLE kordss ALTER COLUMN field10 TO odosat REAL
ENDIF
IF vcol10 = 'odo (mg/l)' THEN
ALTER TABLE kordss ALTER COLUMN field10 TO odo REAL
ENDIF
IF vcol10 = 'pH' THEN
ALTER TABLE kordss ALTER COLUMN field10 TO phlevel REAL
ENDIF
-- Rename the constant cells
ALTER TABLE kordss ALTER COLUMN field1 TO rundate DATE
ALTER TABLE kordss ALTER COLUMN field2 TO runclock TIME
ALTER TABLE kordss ALTER COLUMN field3 TO site TEXT (6)
ALTER TABLE kordss ALTER COLUMN field4 TO dataid TEXT (8)
ALTER TABLE kordss ALTER COLUMN field5 TO temperature REAL
ALTER TABLE kordss ADD COLUMN pondid INT
ALTER TABLE kordss ADD COLUMN importstamp GUID
-- Stamp all rows with the same GUID
SET VAR vnewguid = (CVAL('GUID'))
UPDATE kordss SET importstamp = .vnewguid
-- Get rid of header row
DELETE ROW FROM kordss WHERE rundate IS NULL
