You are viewing limited content. For full access, please sign in.

Discussion

Discussion

Auto Import .csv into SQL Table??

posted on April 2, 2015

Is there a way to have a .csv imported into a SQL Table automatically?  I know how to do it manually, but there is a situation where a .csv is exported nightly from PeopleSoft and we want that imported automatically into SQL Table to use with Laserfiche.  If there's a way, can anyone point me in that direction (I'm not a SQL expert)!!

 

Thanks!

0 0
replied on April 3, 2015 Show version history

You can do this by creating the table in SQL first with the appropriate data types. You can do this by importing into SQL first and then write your script to update the table. You would need to create a .bat file in order to run the SQL scripts.

The command for the .bat file would be something similar to this:

sqlcmd -S ServerName -U UserName -P Password -i "C:\newfolder\update.sql" -o "C:\newfolder\output.txt"

This creates an output file that will specify errors

If you are using a .csv file you would write into a text file and change the extension to .sql

You can use the following command:


BULK INSERT DBName.dbo.TableName
    FROM 'C:\sqlscript\FileName.csv'
    WITH
    (
        FIRSTROW = 2,
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n'
    )
GO

or this command

INSERT INTO DBName.dbo.TableName ([Employee_ID], [FirstN], [LastN], [PhoneN])
SELECT A.[Employee_ID], A.[FirstN], A.[LastN], A.[PhoneN]
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\sqlscript\FileName.csv', 'select * from [TableName]') AS A
ORDER BY [Employee_ID];
GO

Edit:

You can set the .bat file to be scheduled within windows task scheduler.

0 0
replied on April 2, 2015

You can create an SSIS package and then schedule it to run automatically. See this page for an overview of how to use the SQL Server Import and Export Wizard to create your SSIS package and then this page for how to automate the execution of it. There are other resources available as well.

1 0
replied on April 2, 2015

I think you are asking something that should be easily done with the right thought and setup, but you may do better asking this in a non-Laserfiche specific area. Try stackoverflow for an answer or ask the question yourself. I am sure someones written a script that can be modified

0 0
replied on April 2, 2015

Agreed...but figured I'd try here first as I'm more familiar with this forum than others.

0 0
replied on April 2, 2015 Show version history

Daryl - Random thought but once you get the SQL syntax down correctly you could use it to create a SQL stored procedure and then call that stored procedure from a timed workflow using the Custom Query activity.

0 0
replied on April 3, 2015

Please don't use Workflow as a substitute for SQL's import tools. It will not give you anything comparable in terms of performance.

1 0
replied on April 3, 2015

Yes, there is often a trade-off between workflow performance and ease of use, and control over a specific process.  The correct configuration of a potential solution involving workflow is dependent on many variables, including, but not limited to performance.

0 0
replied on April 2, 2015

Does the MSSQL function BULK INSERT solve your problem?  Or can you clarify what you see as the difference between manual and automatic?

1 0
replied on April 2, 2015

There's an export function that happens on a nightly basis from PeopleSoft that puts a .csv file into a windows directory.  Right now, the client manually reviews this file, but we want to incorporate the data on that file into Laserfiche templates to trigger workflows, etc.  So to take the "manual" process out of it, I'd like to know if there's a way to have that file automatically imported into a SQL table on a scheduled basis so that it isn't dependent on people doing it.

0 0
replied on April 2, 2015

If you want it to trigger Workflows, you can't update SQL directly.  You need to go through the SDK so that LFS is aware of the changes and can notify WF.

0 0
replied on April 2, 2015

Brian, I'm not looking to have it trigger workflow, I'm looking for it to have the data put into the table so that my workflow can do queries and trigger certain activites based upon the queries.  I just need to get the .csv data into sql in an automated fashion. Sorry for the confusion.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.