Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Import, Append and Split

    Hi,

    To import and append the data in the existing master sheet which have records in them, While importing the data, column E all 24 digits to be imported then the zeros are to be trimmed in this manner, from right it will go upto left 16 digits [removes all the zeros before that] then move from left towards right for 8 digits all others after that are to be trimmed, then to be saved as numeric without decimal.

    Secondly Copy & Append the row to the desitination sheets from Column B, if particular sheet is not available then create and copy them.

    For reference i have attached the sample.txt and sample.xlsx

    Help required.
    Attached Files Attached Files
    Last edited by zmagic; 2014-10-16 at 09:52.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi

    Copy the attached files to a test folder of your choice.

    In the attached macro .xlsm file, I have deleted the sheet [19] to show how a new sheet will be created if it doesn't exist.
    Click the button [Select text file and process].
    Select the sample text file (you provided previously, added here again)

    New sheets are added at the beginning of the workbook.
    It would be possible to 'sort' the sheets in 'numeric' order

    I have commented the vba code to help you.

    zeddy
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Zeddy,

    I neeed your advise/decision.
    This data which we will append in the sheet will grow to large voulme approx. 2/3 Hundred Thousand Serials will be stored from Column F onwards.
    Is this feasible to have all this data in single workbook with 50 sheets or should we create single workbook for each of them and then append the data to it. 19,3,11... So in the stage 2 it will be a easier and faster to reach for the results.

    The scenario is that after this I will import one more text file which is about 5000 thousand rows of data and on daily basis this will be done.
    This newly imported file will search for particular serial in that generated sheet/workbook. 19,3,11.. The Col B will call the link sheet and our key search is Col E (Data sheet) once the match is found in sheet then it will copy the Col B value to Data Sheet G and Col C & D value to Data sheet K,L.

    Please have a look to workbook for results.

    Awaiting for advise.
    Attached Files Attached Files
    Last edited by zmagic; 2014-10-18 at 07:20.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    zmagic,

    If needed, here is a different approach from Zeddy's. The code will append the text to the Master sheet and convert the numbers in column E as you indicated. It will then copy and append each line to the worksheet indicated by the value in column B for that line. If the sheet does not exist then it will be created. Based on the sample.txt file you provided in Post#1

    HTH,
    Maud

    Code:
    Sub FormatInput()
    On Error Resume Next
    Application.ScreenUpdating = False
    '---------------------------------------
    'DECLARE AND SET VARIABLES
    Dim Sht As Worksheet
    Dim LastRow As Integer, EndRow As Integer
    Dim FileToOpen As String
    Worksheets("MASTER").Select
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Application.DisplayAlerts = False
    '---------------------------------------
    'IMPORT TEXT FILE
    FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    s = Split(FileToOpen, "\") 'USED TO DELETE CONNECTION
    t = Split(s(UBound(s)), ".")  'USED TO DELETE CONNECTION
    If FileToOpen = "False" Then Exit Sub
    With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & FileToOpen, Destination:=Range("$A$" & LastRow))
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, xlTextFormat)
            .Refresh BackgroundQuery:=False
    End With
    '---------------------------------------
    'CYCLE THROUGH IMPORTED LINES
    For I = LastRow To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Cells(I, 5) = Val(Mid(Cells(I, 5), 8, 9)) 'CONVERT 24CHAR STRING
    '---------------------------------------
    'CREATE NEW SHEET IF NEEDED BASED ON COL B
        For Each Sht In ThisWorkbook.Worksheets
            If Cells(I, 2) = Sht.Name Then Sht.Activate: GoTo Skip
        Next Sht
        Worksheets.Add After:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = Worksheets("Master").Cells(I, 2)
    Skip:
    '---------------------------------------
    'COPY IMPORTED LINES TO SHEET
        With Worksheets("Master")
        EndRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Cells(EndRow + 1, 1) = .Cells(I, 1)
        Cells(EndRow + 1, 2) = .Cells(I, 3)
        Cells(EndRow + 1, 3) = .Cells(I, 4)
        Cells(EndRow + 1, 4) = .Cells(I, 5)
        .Activate
        End With
    Next I
    '---------------------------------------
    'CLEANUP
    Columns("A:E").AutoFit
    ActiveWorkbook.Connections(t(0)).Delete
    Sht = Nothing
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Maudibe,

    Your codes are faster. I need a small change in Column B (sheet names) in Column B if 3 then create sheet as 100000003, if its 19 then 700000019 i have a list for all of them. Its 1 to 50 and i have all the parent numbers.

    Secondly, once the data is appended to their sheet save all the sheet in CSV (comma delimited) in c:
    bkup folder as their sheet names, if existing CSV files in the folder to overwrite with newly created file.
    Thanks in advance for your support.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    z,

    Sounds doable. Can you provide the conversion list of B col values to sheet tab names. If you are sending the values to their perspective worksheet then creating workbooks from them, does it matter to you if we forget about writing to each worksheet and instead, write directly to their perspective CSV file? A sample of how you want the destination file to look with column headers would be nice.

    Maud

  7. #7
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Maudibe for helping,

    Write directly to their perspective CSV file?
    I have created the conversion list in the attached workbook sheet.

    Regarding writing directly to their perspective CVS was not in mind, but if its possible than what else i want, very nice of you.
    The data will be appended to the existing CVS files while maintaining all fields and their leading & trailing zeros.
    Sample file attached in zip format.

    The column headers are not necessary.


    Thanking you in advance.
    Attached Files Attached Files

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    z,

    Your welcome. Getting the file name conversion should be simple enough with a Vlookup from the conversion table. From the zipped CSV file would I be correct to say that you wanted the 4 fields separated by a pipe symbol within a single cell appending down column A?

    Maud

  9. #9
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi,
    Thanks for the response.

    From the zipped CSV file would I be correct to say that you wanted the 4 fields separated by a pipe symbol within a single cell appending down column A?
    You are absolutely correct, I don't now the future, please don't bound to 4 columns tomorrow the columns may increase, so what ever must be appended.

  10. #10
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi Maudibe,

    After the last post there is no response from your side, please reply with udpate i am awaiting.

    Thanking you in adavance

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Working on it z. Will have solution completed tonight or tomorrow

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    z,

    Here is the revised file that will:
    1. Append the text to the Master sheet
    2. Convert the numbers in column E as you indicated.
    3. Copy and append each line to the worksheet indicated by the value in column B for that line.
    4. If the sheet does not exist then it will be created.
    5. Copy each sheet to a CSV file with the same name as the sheet located in the c:\bkup folder (new)
    6. If the file exists, it will overwrite with the current data. (new)
    7. If the file does not exist, then it will create it. (new)
    8. You will receive a message that the data has successfully been transferred (new)

    Make sure you have created the C:\bkup folder. Based on the attached sample.txt file. Both the .txt file and the revised file can be located anywhere on your hard drive

    HTH,
    Maud
    Attached Files Attached Files

  13. #13
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks Maudibe.

    I have tested the codes & i need some changes to it.

    Is this possible to only export selected data which we will import daily to those existing CSV file, adding the new data to the end of existing CSV file in folder rather than overwriting the entire file ? Which will take a lot of time once the database gets larger.

    Reagarding copying of cells i don't want to restrict it copy all the columns from Master Sheet to csv file.

    COPY IMPORTED LINES TO SHEET
    With Worksheets("Master")
    EndRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Cells(EndRow + 1, 1) = .Cells(I, 1)
    Cells(EndRow + 1, 2) = .Cells(I, 3)
    Cells(EndRow + 1, 3) = .Cells(I, 4)
    Cells(EndRow + 1, 4) = .Cells(I, 5)
    .Activate
    End With
    Next I
    Many thanks in advance.
    Last edited by zmagic; 2014-10-25 at 03:27.

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    z,

    Secondly, once the data is appended to their sheet save all the sheet in CSV (comma delimited) in c:
    bkup folder as their sheet names, if existing CSV files in the folder to overwrite with newly created file
    So, instead of appending the sheet in the master file and overwriting the CVS, you would like to overwrite the sheets in the master file with the new data then append to the csv files? Again, create the csv if it does not exist.

    Is this possible to only export selected data
    Let me know what method you wish to use to select the data to send to the csv files.

    If this is correct, I will gladly adjust the code.

    Maud
    Last edited by Maudibe; 2014-10-25 at 09:17.

  15. #15
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi Maudibe,

    Let me put it simply, day one we have some text data we use your script for create & bifurcate for CSV files, job completed.
    day two we receive new data now we only want to bifurcate it and append / export it to those existing csv files.

    Please don't restrict the append column to 4 because i have many more column than i described it in my example.

    Thanking you in advance.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •