Consolidation Export File Modifications (Cleansing) 1

[iwells]

Hi,

We have lots of issues with carriage returns appearing in our consolidation import files (of which we have 15-20) so I created a scrubbing program using a macro, but the file it creates is missing something and I can't figure out what. Here's the loop:

Option Explicit

Public objFSO As Object
Public objFileFrom As Object
Public objFileTo As Object

Private Sub Initialize()
Dim strTemp As String
Dim strLineTo As String


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFileFrom = objFSO.OpenTextFile("H:\sage\Consolidation Scrubber\Sample File\INFILE.001", 1)
Set objFileTo = objFSO.CreateTextFile("H:\sage\Consolidation Scrubber\Sample File\OUTFILE.001", 1)

Do Until objFileFrom.AtEndOfStream

strLineTo = objFileFrom.ReadLine

If Mid(strLineTo, 2, 1) = 2 Then

If Len(strLineTo) < 383 Then

Do
strTemp = objFileFrom.ReadLine
strLineTo = strLineTo & strTemp

Loop While Len(strLineTo) < 381
End If
End If

objFileTo.WriteLine strLineTo
Loop

objFileFrom.Close
objFileTo.Close
End Sub

When I open the OUTFILE.001 up in a file editor like Notepad++ (I actually use Geany) it looks to be in the correct format and it will be brought in to the consolidation import screen, but when I click Import is says no records are found.

I uploaded the first record I'm working with to see if anyone can tell me what I'm missing?

Thanks!

 

[ettienne]

My question would be why do you have a problem which carriage returns in your import files?
What is creating these import files?

Sage 300 Certified Consultant

 

[ettienne]

The file you uploaded is missing the second detain line for the journal entry.
Accounting 101: for each debit there should be a credit - so you should have at least 2 detail lines.

Sage 300 Certified Consultant

 

[iwells]

I'll post the adjusted file on Monday when I'm back in the office, but the reason we have carriage returns in the file is if users accidentally hit enter or a special character during data entry or somehow it appears in an Excel file that's used for imports it carries over in to the consolidation export. Unfortunately we've tried training users but it still come up and we have 15-20 branches so this is generally an issue for a few branches a month and manually fixing them is becoming too time consuming for our users to fix.

The code I posted is run through a macro.

 

[ettienne]

Ah now I see, I had the same problem with one particular user. They were copy/pasting descriptions from Excel and Excel inserts a CR+LF in the text you paste, then that blows up the export/import. I have a macro that cleans the GLPOST table and strips out the CR+LF characters, that solves the problem at the source. The macro runs a SQL query to replace CR+LF with nothing.

Sage 300 Certified Consultant

 

[iwells]

I'd be on board with that except it means deploying 20 or so macros and expecting the end user to keep those up to date as opposed to creating 1 and deploying that at the consolidating office.

 

[iwells]

Outside of the DR/CR not matching to you see anything because all I did was delete all the other transactions from the file and modified the dollar amounts and description (sensitive data).

I exported the full file to excel and it balances and like I said, it comes in to the window, but when I import it says there are no transactions so I feel like something is wrong with the CRLF of something else.

 

[ettienne]

It is one macro, you can run it in all companies. If you want to you can have it run through all companies and clean the data, much more efficient that way.

Sage 300 Certified Consultant

 

[iwells]

Our companies are all separate database on separate servers and etc ... a little more difficult than that, but I definitely need to give that more thought.

 

[ettienne]

I would still have them run the macro before exporting.

Sage 300 Certified Consultant

 

[ettienne]

Here is the macro to zap the CR+LF from the GLPOST table.
This updates the Reference field, you can do the same for the Description field.

************************
Dim mDBLink As AccpacCOMAPI.AccpacDBLink
Dim GLPost As AccpacCOMAPI.AccpacView
Set mDBLink = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
mDBLink.OpenView "CS0120", GLPost
GLPost.Browse "update GLPOST set JNLDTLREF = REPLACE(JNLDTLREF,CHAR(13)+CHAR(10),'') where charindex(JNLDTLREF,CHAR(13)+CHAR(10)) > 0", True
GLPost.Close
mDBLink.Close
Set GLPost = Nothing
Set mDBLink = Nothing
**********************

Sage 300 Certified Consultant

 

[iwells]

Awesome, thank you!