Jump to content

Importing a CSV but only want one record per value?


BowDown
 Share

This topic is 5754 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Ok here's an example of what I'm pulling from the CSV:

ALSl #1,241391A,Fri Dec 2 7:46:3 2005

ALSl #1,241391A,Fri Dec 2 7:46:7 2005

I have 3 fields setup in my table:

sawUsed - text

jobKey - text

dateCut - text

It imports the file correctly.. but I only want one record of the job to import. I don't really care how many pieces have been cut (it will give me a new line with the same job key for every piece it cuts). I just want it to read the jobKey in as 1 record, then skip all the other entries till the jobKey changes.

I don't care if it overwrites the record when it finds a duplicate jobKey.. but I just don't want it creating 100 records for each jobKey.

Link to comment
Share on other sites

Or if deploying an ODBC setup is a problem you could import all CSV records into a dummy/temp temple in your FM file and have a script parse through that temp temple creating new records in your target table.

Having said that; mind posting the VBscipt here so that others can learn? Or in the Windows Automation forum. We need posts like this.

Link to comment
Share on other sites

Ok, what this script does is the following:

I goes out to 2 CSV logs from my saws. It pulls in the information, checks for dupicates, then does some formatting on the information, and spits it back out to another CSV file. The main reason I use a temp file is incase the script crashes, I can use it for troubleshooting.

From there it reads the CSV back in, and pushes each line into FileMaker as a new record.

I also have it emailing me when it does the update. This is because the script will be run on my server when I finish the FM8 design.

' This script organizes the saw's log files into useful,

' FileMaker friendly import.

'-----------------------------------------------------------

'Setup Constants and Variables

'-----------------------------------------------------------

Const ALS_Log = "I:LOGSals.log"

Const AM_Log = "I:LOGSSAWDATA.LOG"

Const ALS_SAW = "I:LOGSals_saw.csv"

Const AM_SAW = "I:LOGSam_saw.csv"

Dim counter

Dim fso, f

Dim theWriteFile, theReadFile, removeFile

Dim retTRS(50000), retDate(50000)

Dim outString

Dim splitString

Dim holderTRS, holderDate, breakDateUp, shortDate

Dim SQLQuery

Dim SQLUpdateQuery

Dim currentSawRecord, shift

Set fso = CreateObject("Scripting.FileSystemObject")

'-----------------------------------------------------------

'Read in the Log File Contents and put into an Array (ALS)

'-----------------------------------------------------------

'ALS

Set theReadFile = fso.OpenTextFile(ALS_Log, 1, false)

counter = 0

do while theReadFile.AtEndOfStream <> true

splitString = Split(theReadFile.ReadLine, chr(44), 25)

if not (Trim(splitString(14)) = "") then

if not (Trim(splitString(23)) = "") then

retTRS(counter) = Left(Trim(splitString(14)), 6)

retDate(counter) = Trim(splitString(23))

end if

end if

counter = counter + 1

loop

theReadFile.Close

'Put info into the holders for compairson

counter = counter - 1

do while (retTRS(counter) = "")

counter = counter - 1

loop

holderTRS = retTRS(counter)

holderDate = retDate(counter)

counter = counter - 1

'-----------------------------------------------------------

'Output Array contents to a file

'-----------------------------------------------------------

Set theWriteFile = fso.OpenTextFile(ALS_SAW, 2, true)

breakDateUp = Split(holderDate, " ", 5)

shortDate = CDate(breakDateUp(1) + " " + breakDateUp(2) + ", " + breakDateUp(4))

theWriteFile.WriteLine "ALS" + chr(44) + holderTRS + chr(44) + CStr(shortDate) + chr(44) + Replace(Left(breakDateUp(3), 2), ":", "")

do until counter = -1

if not (retTRS(counter) = holderTRS) and not (retTRS(counter) = "") then

holderTRS = retTRS(counter)

holderDate = retDate(counter)

breakDateUp = Split(holderDate, " ", 5)

shortDate = CDate(breakDateUp(1) + " " + breakDateUp(2) + ", " + breakDateUp(4))

theWriteFile.WriteLine "ALS" + chr(44) + holderTRS + chr(44) + CStr(shortDate) + chr(44) + Replace(Left(breakDateUp(3), 2), ":", "")

end if

counter = counter - 1

loop

theWriteFile.Close

'-----------------------------------------------------------

'Read the file in, and push it into FileMaker

'-----------------------------------------------------------

Set OBJdbConnection = CreateObject("ADODB.Connection")

OBJdbConnection.Open "sawTracker.fp7", "Admin"

Set theReadFile = fso.OpenTextFile(ALS_SAW, 1, false)

counter = 0

do while theReadFile.AtEndOfStream <> true

splitString = Split(theReadFile.ReadLine, chr(44), 4)

if (splitString(3) < 17) then

shift = "D"

else

shift = "N"

end if

SQLUpdateQuery = "INSERT into sawTracker values ('" + splitString(0) + "', '" + splitString(1) + "', '" + splitString(2) + "', '" + shift + "')"

OBJdbConnection.Execute SQLUpdateQuery

counter = counter + 1

loop

OBJdbConnection.Close

theReadFile.Close

if fso.FileExists(ALS_Log) then

Set removeFile = fso.GetFile(ALS_Log)

removeFile.Delete True

Set removeFile = Nothing

end if

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------

'Read in the Log File Contents and put into an Array (AM)

'-----------------------------------------------------------

'AM

Set theReadFile = fso.OpenTextFile(AM_Log, 1, false)

counter = 0

do while theReadFile.AtEndOfStream <> true

splitString = Split(theReadFile.ReadLine, chr(44), 25)

if not (Trim(splitString(14)) = "") then

if not (Trim(splitString(23)) = "") then

retTRS(counter) = Left(Trim(splitString(14)), 6)

retDate(counter) = Trim(splitString(23))

end if

end if

counter = counter + 1

loop

theReadFile.Close

'Put info into the holders for compairson

counter = counter - 1

do while (retTRS(counter) = "")

counter = counter - 1

loop

holderTRS = retTRS(counter)

holderDate = retDate(counter)

counter = counter - 1

'-----------------------------------------------------------

'Output Array contents to a file

'-----------------------------------------------------------

Set theWriteFile = fso.OpenTextFile(AM_SAW, 2, true)

breakDateUp = Split(holderDate, " ", 5)

shortDate = CDate(breakDateUp(1) + " " + breakDateUp(2) + ", " + breakDateUp(4))

theWriteFile.WriteLine "AM" + chr(44) + holderTRS + chr(44) + CStr(shortDate) + chr(44) + Replace(Left(breakDateUp(3), 2), ":", "")

do until counter = -1

if not (retTRS(counter) = holderTRS) and not (retTRS(counter) = "") then

holderTRS = retTRS(counter)

holderDate = retDate(counter)

breakDateUp = Split(holderDate, " ", 5)

shortDate = CDate(breakDateUp(1) + " " + breakDateUp(2) + ", " + breakDateUp(4))

theWriteFile.WriteLine "AM" + chr(44) + holderTRS + chr(44) + CStr(shortDate) + chr(44) + Replace(Left(breakDateUp(3), 2), ":", "")

end if

counter = counter - 1

loop

theWriteFile.Close

'-----------------------------------------------------------

'Read the file in, and push it into FileMaker

'-----------------------------------------------------------

Set OBJdbConnection = CreateObject("ADODB.Connection")

OBJdbConnection.Open "sawTracker.fp7", "Admin"

Set theReadFile = fso.OpenTextFile(AM_SAW, 1, false)

counter = 0

do while theReadFile.AtEndOfStream <> true

splitString = Split(theReadFile.ReadLine, chr(44), 4)

if (splitString(3) < 17) then

shift = "D"

else

shift = "N"

end if

SQLUpdateQuery = "INSERT into sawTracker values ('" + splitString(0) + "', '" + splitString(1) + "', '" + splitString(2) + "', '" + shift + "')"

OBJdbConnection.Execute SQLUpdateQuery

counter = counter + 1

loop

OBJdbConnection.Close

theReadFile.Close

if fso.FileExists(AM_Log) then

Set removeFile = fso.GetFile(AM_Log)

removeFile.Delete True

Set removeFile = Nothing

end if

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------------------------------------------

Set objMessage = CreateObject("CDO.Message")

objMessage.Subject = "FileMaker sawTracker Update Is Complete"

objMessage.From = "xxx"

objMessage.To = "xxx"

objMessage.TextBody = "This email is just to notify you that the script has completed."

'==This section provides the configuration information for the remote SMTP server.

'==Normally you will only change the server name or IP.

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "email.srsloan.com"

'Server port (typically 25)

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

Edited by Guest
Link to comment
Share on other sites

Ya, the data source name is sawTracker.fp7, and I'm using the DataDirect 5.4 driver for ODBC off the website.

That was a bit tricky to setup. Couldn't really find clear directions. So it was alot of guess and check.

For ODBC:

Turn on sharing via ODBC in fileMaker for the file.

When setting up the DirectData Driver put in the following:

Source Name: (I used sawTracker.fp7)

Description: Whatever you want.

Don't check Use LDAP.

SequeLink Server Host: 127.0.0.1

SequeLink Server Port: 2399

Then click the "..." button and select the shared source you wanna connect to.

Link to comment
Share on other sites

This topic is 5754 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.