Skip to Content
author's profile photo Former Member
Former Member

problem with flat file upload with ASCII file

Hi All,

I am trying to load data into an ODS object via PSA using a text file (tab delimited). I have the following settings in my InfoPackage

External Data tab: ASCII File

Seperator for thousands:

Character for decimal point:

Currency Conversion:

Number of Header rows to be ignored: 1

Processing tab: Only PSA and Update into data targets subsequently

When I do a preview the data is not showing up in the right format....the tab seperation does not work and data is garbled with extra hexadecimal characters too.

The same file contents in .csv format is loading the data in the right format and preview is fine too.

I need help trying to convert this csv file to .txt format.

Any clues are highly appreciated

Thank you

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Oct 12, 2005 at 10:17 PM

    Hi...

    Can I know why do u want to load the data via a text file, if .csv is working fine!

    Thanks,

    Sai.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Sai,

      THe file has to be uploaded from an application server via a process chain and .txt format is recommended. I am using .csv file only for test purposes in Sandbox. Ultimately in Production we have to use .txt format only

  • author's profile photo Former Member
    Former Member
    Posted on Oct 12, 2005 at 10:21 PM

    Hi

    I find it easyer to work with csv files.

    all you need to do is convert the txt file to csv.

    is this a problem?

    Edan

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 13, 2005 at 10:42 AM

    Hi,

    If in the infopackage you click on a File Type ASCII-file (CR delimiter) radiobutton and then F1, you’ll see the help:

    “With an ASCII file as a data source, the data has to be available as a string in the format of the transfer structure. There are no data separators, empty characters are not ignored.”

    This means that:

    - you shouldn’t use any delimiters among the record fields

    - all fields should have the length the system expects (length of the appropriate infoobject in transfer structure)

    - each record should be separated from the next by carriage return CR.

    Since I think the program that converts file from Excel format into CR-delimited would be very useful, I created such program in VBA.

    So, open your Excel file (if you have csv-file you can open it using Excel).

    The program assumes that the first row of the file contain field names. Data itself begin from the 2nd row. Insert as the 1st and 2nd rows the new, blank rows.

    Into A1 cell insert number of the last row with data, into B1 – number of the last column. Certainly, it may be determined in the program, but I have not done it – if someone wants it, s/he can try it.

    Into fields of the 2nd row insert expected length of the field.

    Make sure that “Visual Bacis” toolbar is seen. (If not – tick in menu path: View/Toolbars/Visual Basic).

    In this toolbar click on a ‘Design Node’ icon.

    Drag from the toolbar and drop into worksheet a ‘Command button’ element.

    Double click on the button. In the open window for “Private Sub CommandButton1_Click()” subroutine insert the following code:

    Dim ws1 As Worksheet

    Dim J As Long, I As Long, L As Long

    Dim FileName As String, Out As String, formatOut As String

    Dim LenCell As Long

    Set ws1 = ThisWorkbook.Worksheets("SHEET1")

    FileName = ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 3) & "txt"

    Open FileName For Output As #1

    For J = 4 To ws1.Cells(1, 1)

    Out = ""

    For I = 1 To ws1.Cells(1, 2)

    formatOut = (ws1.Cells(J, I))

    L = Len(formatOut)

    LenCell = Val(ws1.Cells(2, I))

    If L > LenCell Then

    formatOut = Left(formatOut, LenCell)

    Else

    Do While L < LenCell

    formatOut = formatOut & " "

    L = L + 1

    Loop

    End If

    Out = Out & formatOut

    Next

    Print #1, Out

    Next

    Close #1

    Set ws1 = Nothing

    MsgBox "File transformation finished!", vbOKOnly

    Now return to Excel sheet, click again ‘Design Node’ icon switching into run mode.

    When you press on this button now, the ASCII program will be created. It will have the same name as an input file (and in the same directory), but with TXT extention.

    Remarks:

    - The program works with the current worksheet named as “SHEET1”.

    - Fields that are shorter than those expected by the system are padded by spaces from the right. It should not be the problem, since in transfer structure you can check symbols only flag.

    - Longer fields are truncated to the length expected.

    Best regards,

    Eugene

    Message was edited by: Eugene Khusainov

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.