Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Exce help needed - How do I convert to a database loadable csv file

I believe there must be an easier way to format the data in an excel spreadsheet
Can someone please help me out here???
Here is the problem.
The data looks as follows

StoreId,product,        price  StoreId,product,        price
1           ,Castle Toy,    30.00  2           PinePen        12.50
1            SeaPlane,     45.00   2           GumDrop     5.77
1            HoolaHup6, 12.67   2           UniveralFan 26.99   ...

The data is broken out across the columns by storeID e.g 3,4,5,6... N
Right now I copy and paste each 2 columns in an effort to create a 2 field list for loading
How would I do this in a more efficient manner in  Excel???
StoreId,product,        price
1           ,Castle Toy,    30.00
1            SeaPlane,     45.00  
1            HoolaHup6, 12.67
2           PinePen        12.50
2           GumDrop     5.77
2           UniveralFan 26.99


I imagine for the skilled Excel person this is simple - Please advise!!
0
Robert Silver
Asked:
Robert Silver
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
are the column names in excel exactly like this

StoreId,product,        price  StoreId,product,        price
0
 
Patrick MatthewsCommented:
A sample file would be helpful...
0
 
Rey Obrero (Capricorn1)Commented:
if the columns is what you posted,
change column names like this

StoreId,product,        price  StoreId1 ,product1,        price1

then, from access db, create a link to the excel file..

you can now combine the columns with a union query


select StoreId,product, price from tableExcel
union all

select StoreId1,product1, price1 from tableExcel
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
redmondbCommented:
Hi, rssdds.

I couldn't decode how the input data was organised, so in the attached I've taken a chance and assumed that there shouldn't be a space in ",Castle Toy,". (Although, now that I look at it again, is it possible that a comma before a string is a text qualifier?)

Please change the text file's path and name as necessary.
Option Explicit

Sub Split_Stores()
Dim i          As Long
Dim j          As Long
Dim xLast_Col  As Long
Dim xLast_Row  As Long
Dim xLast_Dest As Long
Dim xLast_Item As Long
Dim xSrce      As Worksheet
Dim xDest      As Worksheet

Application.ScreenUpdating = False
    
    Workbooks.OpenText Filename:="D:\Store_Details.txt", _
            Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
            Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1)), _
            TrailingMinusNumbers:=True
    Set xSrce = ActiveWorkbook.Sheets(1)
    
    If xSrce.UsedRange.Rows.Count < 1 Then Debug.Print "!?"
    xLast_Row = xSrce.Range("A1").SpecialCells(xlLastCell).Row
    xLast_Col = xSrce.Range("A1").SpecialCells(xlLastCell).Column
    
    If xLast_Row < 2 Or xLast_Col Mod 3 <> 0 Then
        xSrce.Activate
        MsgBox ("Invalid rows (" & xLast_Row & ") or columns (" & xLast_Col & ") - run cancelled.")
        Exit Sub
    End If
    
    Set xDest = ThisWorkbook.Worksheets.Add
    xDest.Range("A1:C1") = Array("Store ID", "Product", "Price")
    
    For i = 1 To (xLast_Col - 2) Step 3
        xLast_Item = xSrce.Cells(Rows.Count, i).End(xlUp).Row
        If xLast_Item > 1 Then
            xLast_Dest = xDest.Range("A1").SpecialCells(xlLastCell).Row
            xSrce.Range(xSrce.Cells(2, i), xSrce.Cells(xLast_Item, i + 2)).Copy Destination:=xDest.Range("A" & xLast_Dest + 1)
        Else
            MsgBox ("No data from column no. " & i & " - missing store bypassed.")
        End If
    Next
    
    xDest.Columns("B:B").Replace What:=",", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    xSrce.Parent.Close savechanges:=False
    
    xDest.Activate
    
Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.
Split-Stores-V2.xlsm
Store-Details.txt
0
 
Robert SilverSr. Software EngineerAuthor Commented:
I can program - I am looking for a quick Excel is solution like maybe a menu  method or such...  It seems to me there should be a way to facilitate it

I will get back to you in a week
0
 
redmondbCommented:
rssdds,

Pasting data directly into a question can cause layout problems, so please post a larger sample as a text file. This may give us a clearer view of the file's format.

Also, do you want the commas in the output list?

Brian.
0
 
Robert SilverSr. Software EngineerAuthor Commented:
I finally got to this.  Brian what References does this code require on an Access 2010
to run or do I need to run it in Excel? If so how? I am only aware of running it in Access.
Not a lot of error messages display to tell me which libraries I need

As soon as I run this in Access 2010 successfully you get A+ points but now I struggle to know what references the subroutine needs. I even tried to change it into a function that returns a variant and running in in the immediate window: ? split_Stores()
So far I have :
     Visual Basic For Applications
     Microsoft Access 14.0 Object library
     OLE Automation
     Microsoft Office 14.0 Access database engine Object Library
   
The compile shows Worksheet is a user defined type not defined after compiling....
0
 
redmondbCommented:
rssdds,

As soon as I run this in Access 2010 successfully...
You'd repeatedly mentioned Excel assistance in an Excel Topic so I provided an Excel solution. At this point, let's continue with that and check that the logic's correct. Once that's done, we can convert it to Access.

...you get A+ points
Only "A" and only when you've got the solution you need.

do I need to run it in Excel? If so how?
(1) Open Split-Stores-V2.xlsm.
(2) Edit its Split_Stores() macro to reflect your file's name and path.
(3) Click the blue button on Sheet1.

Brian.
0
 
Robert SilverSr. Software EngineerAuthor Commented:
Note this module must be tailored a bit but it seems to work what it does is create a worksheet output as a list of data from the horizontal positions of repeating data down and across

No need to Access -ize it but note Microsoft does not make this easy at all especially if you worked on versions of Office 2003 and backwards.
This must be loaded into a module and run within Excel!
0
 
redmondbCommented:
rssdds,

This must be loaded into a module and run within Excel!
Nope - it's already there!

what it does is create a worksheet output as a list of data from the horizontal positions of repeating data down and across
Other than some uncertainty around the delimiter, it seems to produce exactly the sample output in your question

Note this module must be tailored a bit
Other than the text file's path and name that I mentioned, is there anything else that needs changing?

Brian.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now