Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-01-18
10
Medium Priority
?
193 Views
Last Modified: 2013-02-27
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
Comment
Question by:Robert Silver
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38795121
are the column names in excel exactly like this

StoreId,product,        price  StoreId,product,        price
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38795164
A sample file would be helpful...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38795175
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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 38795369
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
 
LVL 2

Author Comment

by:Robert Silver
ID: 38795968
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38796369
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
 
LVL 2

Author Comment

by:Robert Silver
ID: 38935755
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38936032
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
 
LVL 2

Author Closing Comment

by:Robert Silver
ID: 38936177
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38936248
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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question