Import an excel file -> on a regular basis...

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Import an excel file -> on a regular basis...

Steven Evans
Good morning,

I have spent some time in the archives, but am wondering if there is  
actually an easy (and safe) way to do this.

I am working on a research project where I am collecting data from  
subjects during two week periods.  As I collect their data during the  
two weeks, they also take a breathalyzer test which stores the data  
locally.  At the end of the two weeks, I download the data from the  
breathaylzer, which is exported as a text file....

Ultimately, I would like for a research assistant to upload the text  
file to a database, importing it into a temporary table, where the RA  
can then match it with the appropriate matching record in the  
existing db.  (IE...match the breathalyzer test from 1/5/2008 with  
the existing db questions from  that date).  The backend lasso is  
easy, I just can't seem to make an easy import script....

I would like this to be a custom built item that I can add to the  
admin interface for  this site, in lieu of forcing them to open  
navicat, phpmyadmin, etc, etc....if possible...

Thanks.

sqe



Steven Q. Evans
Director of Technological Resources
MSI Productions
http://www.msiprod.com
(858)344-3351 • (858)348-0629 • (707)215-1032 Fax • [hidden email]



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Import an excel file -> on a regular basis...

Viaduct Productions
You might want to look at the power of FileMaker.  It was tweaked at  
playing pretty with FMP some time ago, that might be of use.  Or,  
have your source change the source.

That is the problem with proprietary formats, but spreadsheets don't  
really offer any alternatives.



On Jan 4, 2008, at 1:21 PM, Steven Q. Evans wrote:

> I have spent some time in the archives, but am wondering if there  
> is actually an easy (and safe) way to do this.


Cheers

Rich in Toronto


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Import an excel file -> on a regular basis...

stevepiercy
In reply to this post by Steven Evans
Navicat can now import Excel worksheets and you can save it as a script.  Use the Import Wizard and at the final stage, you can click Save to name and save the script steps.  I use this for importing soccer game schedules.

FileMaker Pro also has this capability.  The interface is a little easier than Navicat.

You can have the person copy and paste the contents of the Excel worksheet into a textarea, submit it, and have Lasso parse the results.  I do this where secretary-type people like Excel, but hate using web sites, to update large data sets.  I have a script that splits on /r then on /t to generate an array of arrays (like a records_array).  From there it is trivial to manipulate the data set.

Finally you could do the same thing as the previous paragraph, but save the file as a tab-delimited text file.  I think the textarea is easier, though.

--steve


On Friday, January 4, 2008, [hidden email] (Steven Q. Evans) pronounced:

>Good morning,
>
>I have spent some time in the archives, but am wondering if there is  
>actually an easy (and safe) way to do this.
>
>I am working on a research project where I am collecting data from  
>subjects during two week periods.  As I collect their data during the  
>two weeks, they also take a breathalyzer test which stores the data  
>locally.  At the end of the two weeks, I download the data from the  
>breathaylzer, which is exported as a text file....
>
>Ultimately, I would like for a research assistant to upload the text  
>file to a database, importing it into a temporary table, where the RA  
>can then match it with the appropriate matching record in the  
>existing db.  (IE...match the breathalyzer test from 1/5/2008 with  
>the existing db questions from  that date).  The backend lasso is  
>easy, I just can't seem to make an easy import script....
>
>I would like this to be a custom built item that I can add to the  
>admin interface for  this site, in lieu of forcing them to open  
>navicat, phpmyadmin, etc, etc....if possible...
>
>Thanks.
>
>sqe
>
>
>
>Steven Q. Evans
>Director of Technological Resources
>MSI Productions
>http://www.msiprod.com
>(858)344-3351 • (858)348-0629 • (707)215-1032 Fax • [hidden email]
>
>
>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Import an excel file -> on a regular basis...

Tom Parker-15
In reply to this post by Steven Evans
Here is some code I used to import data from an Excel timesheet (saved as
Timetest.txt and uploaded via a web form) into a MySQL database.  You might
be able to modify this to meet your needs.

--Tom Parker

[Variable: 'File_Path' = 'TimeTest.txt'] [Variable: 'File_Array' = (Array)]

[If: (File_Exists: $File_Path)]

[Loop:(File_GetLineCount:$File_Path)]

[Variable: 'File_Temp' = (File_ReadLine: $File_Path,

-FileLineNumber=(Loop_Count))]

[Variable: 'Array_Temp' = $File_Temp->(Split: '\t')]

[$File_Array->(Insert: ($Array_Temp))]

[/Loop]

[/If]

[iterate: ($File_Array), (var: 'record')]

[Var:'Staff'=($record->(Get:1)),'Month'=($record->(Get:2)),
'Year'=($record->(Get:3)),
'Client'=($record->(Get:4)),'Project'=($record->(Get:5)),
'Task'=($record->(Get:6)), 'Code'=($record->(Get:7))]

[Loop: -From=8, -To=38, -By=1]

[Var:'Hours'=($record->(Get:(Loop_Count)))]

[If:($hours)>0]

[Inline:-database='database',-Table='Time','staff'=($staff),
'month'=($month),'year'=($year), 'client'=($client), 'project'=($project),
'task'=($task), 'code'=($code), 'day'=(Math_Sub:(Loop_Count),7),
'hours'=($hours),-add]

[/inline]

[/if]

[/Loop]


[/iterate]

----- Original Message -----
From: "Steven Q. Evans" <[hidden email]>
To: "Lasso Talk" <[hidden email]>
Sent: Friday, January 04, 2008 11:21 AM
Subject: Import an excel file -> on a regular basis...


Good morning,

I have spent some time in the archives, but am wondering if there is
actually an easy (and safe) way to do this.

I am working on a research project where I am collecting data from
subjects during two week periods.  As I collect their data during the
two weeks, they also take a breathalyzer test which stores the data
locally.  At the end of the two weeks, I download the data from the
breathaylzer, which is exported as a text file....

Ultimately, I would like for a research assistant to upload the text
file to a database, importing it into a temporary table, where the RA
can then match it with the appropriate matching record in the
existing db.  (IE...match the breathalyzer test from 1/5/2008 with
the existing db questions from  that date).  The backend lasso is
easy, I just can't seem to make an easy import script....

I would like this to be a custom built item that I can add to the
admin interface for  this site, in lieu of forcing them to open
navicat, phpmyadmin, etc, etc....if possible...

Thanks.

sqe



Steven Q. Evans
Director of Technological Resources
MSI Productions
http://www.msiprod.com
(858)344-3351 • (858)348-0629 • (707)215-1032 Fax • [hidden email]



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/





--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Import an excel file -> on a regular basis...

Steven Evans
Thanks, Tom and Steve!

This was very helpful!  I have used a combination of Steve's idea of  
cut and paste with your code and it works great....

Thanks for your help.

sqe


On Jan 4, 2008, at 11:59 AM, Tom Parker wrote:

> Here is some code I used to import data from an Excel timesheet  
> (saved as Timetest.txt and uploaded via a web form) into a MySQL  
> database.  You might be able to modify this to meet your needs.
>
> --Tom Parker
>
> [Variable: 'File_Path' = 'TimeTest.txt'] [Variable: 'File_Array' =  
> (Array)]
>
> [If: (File_Exists: $File_Path)]
>
> [Loop:(File_GetLineCount:$File_Path)]
>
> [Variable: 'File_Temp' = (File_ReadLine: $File_Path,
>
> -FileLineNumber=(Loop_Count))]
>
> [Variable: 'Array_Temp' = $File_Temp->(Split: '\t')]
>
> [$File_Array->(Insert: ($Array_Temp))]
>
> [/Loop]
>
> [/If]
>
> [iterate: ($File_Array), (var: 'record')]
>
> [Var:'Staff'=($record->(Get:1)),'Month'=($record->(Get:2)), 'Year'=
> ($record->(Get:3)), 'Client'=($record->(Get:4)),'Project'=($record->
> (Get:5)), 'Task'=($record->(Get:6)), 'Code'=($record->(Get:7))]
>
> [Loop: -From=8, -To=38, -By=1]
>
> [Var:'Hours'=($record->(Get:(Loop_Count)))]
>
> [If:($hours)>0]
>
> [Inline:-database='database',-Table='Time','staff'=($staff),  
> 'month'=($month),'year'=($year), 'client'=($client), 'project'=
> ($project), 'task'=($task), 'code'=($code), 'day'=(Math_Sub:
> (Loop_Count),7), 'hours'=($hours),-add]
>
> [/inline]
>
> [/if]
>
> [/Loop]
>
>
> [/iterate]
>
> ----- Original Message ----- From: "Steven Q. Evans"  
> <[hidden email]>
> To: "Lasso Talk" <[hidden email]>
> Sent: Friday, January 04, 2008 11:21 AM
> Subject: Import an excel file -> on a regular basis...
>
>
> Good morning,
>
> I have spent some time in the archives, but am wondering if there is
> actually an easy (and safe) way to do this.
>
> I am working on a research project where I am collecting data from
> subjects during two week periods.  As I collect their data during the
> two weeks, they also take a breathalyzer test which stores the data
> locally.  At the end of the two weeks, I download the data from the
> breathaylzer, which is exported as a text file....
>
> Ultimately, I would like for a research assistant to upload the text
> file to a database, importing it into a temporary table, where the RA
> can then match it with the appropriate matching record in the
> existing db.  (IE...match the breathalyzer test from 1/5/2008 with
> the existing db questions from  that date).  The backend lasso is
> easy, I just can't seem to make an easy import script....
>
> I would like this to be a custom built item that I can add to the
> admin interface for  this site, in lieu of forcing them to open
> navicat, phpmyadmin, etc, etc....if possible...
>
> Thanks.
>
> sqe
>
>
>
> Steven Q. Evans
> Director of Technological Resources
> MSI Productions
> http://www.msiprod.com
> (858)344-3351 • (858)348-0629 • (707)215-1032 Fax •  
> [hidden email]
>
>
>
> --
> This list is a free service of LassoSoft: http://www.LassoSoft.com/
> Search the list archives: http://www.ListSearch.com/Lasso/Browse/
> Manage your subscription: http://www.ListSearch.com/Lasso/
>
>
>
>
>
> --
> This list is a free service of LassoSoft: http://www.LassoSoft.com/
> Search the list archives: http://www.ListSearch.com/Lasso/Browse/
> Manage your subscription: http://www.ListSearch.com/Lasso/
>

Steven Q. Evans
Director of Technological Resources
MSI Productions
http://www.msiprod.com
(858)344-3351 • (858)348-0629 • (707)215-1032 Fax • [hidden email]



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/