Question

Forms - Best way to use Excel file as a data source

Forms

Updated April 16

asked on April 15

Hello,

I have a customer that keeps track of some driver information an Excel spreadsheet. These drivers access a few different forms through a Forms installation in the DMZ. We'd like to populate some of the data in this Excel file on some of the forms the drivers use. This Excel file is manually updated periodically by users.

It sounds like it's not ideal to access the Excel file directly, as it'll fail if someone has the file open. Is some sort of scheduled task that writes current data from Excel into a SQL table the best option?

Thanks!

0 0

Replies

replied on April 15

As Chad mentioned, SQL is ideal due to the limited access provided by ODBC Excel drivers.

In addition to workflow, you can also use T-SQL's BULK INSERT to get data in from a csv file.

The following is a sample that imports the contents of a csv file into a temp table in SQL

BULK INSERT [#ImportTemp]
FROM 'filepath\filename.csv'
WITH
(
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n',
	KEEPNULLS
)

We used a temp table to hold the imported data then ran a subsequent MERGE query to update the SQL table rather than trying to overwrite directly from the insert.

We set this up as part of a scheduled SQL job so if the csv was updated the SQL table would get updated the next time the job ran.

3 0

replied on April 15 • Show version history

As @████████and @████████have alluded to, the real answer to "Best way to use Excel file as a data source?" is "Don't".

Any solution where you have Laserfiche directly accessing SQL is highly likely to be fragile.

Any time spent working towards such a solution would be better spent on figuring out how to reliably get the Excel data into a real SQL database, then having Laserfiche access the data in SQL.

3 0

replied on April 15

It really depends on how automated you need it to be.

If you want to read directly from the file at any time you can use the Microsoft JET OLEDB driver to access with it with a workflow script, or ODBC to access it with the workflow query component. This does mean that no one can have it open it when your accessing it though, so best make a copy of it first.

Using a SQL table is the solution to the multiple access problem and you can import from Excel to SQL using the wizard included with SQL Studio. At the point maybe the data should just be managed in SQL instead of Excel.

The Excel file can manually be saved as a CSV file in the repository and parsed with the pattern matching activity in Workflow or via a web browser using javascript in Forms.

2 0

replied on April 16

Write an Excel VBA macro that runs on change and saves the changes to a SQL DB. Use SQL as the source instead.

0 0

You are not allowed to follow up in this post.

Question

Question

Forms - Best way to use Excel file as a data source

Replies

Sign in to reply to this post.