Importing a CSV but only want one record per value?

[BowDown]

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.

[BowDown]

Nevermind. I just wrote a VBScript that would format, and create records in FM8 through ODBC. Much easier that way.

[Wim Decorte]

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.

[BowDown]

Will do tomorrow when I get to work. I'm pretty good at VB in general. I just figured if I could learn how to do it in filemaker that would be another tool in the shed.

[Wim Decorte]

Good, I need allies in showing the FM community how to use the power of VBscript.

[BowDown]

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 December 16, 2005 by Guest

[Wim Decorte]

Excellent!

To avoid confusion, you have DSN named "sawTracker.fp7" and using the FM ODBC driver, pointing to that file set up on the machine right?

[BowDown]

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.

[BowDown]

I will add...

That driver is POS!

I had to go into the registry and delete a couple things just to get it to show up in the drivers list. Yay, I can tell I'm working with quality now.

[Wim Decorte]

Can't argue with that BowDown, not the easiest thing in the world to set up...

On the other hand, FM does XML really well and it's often a lot easier to extract XML than to do the ODBC dance.