Importing CSV as table

Use PowerQuery (Get & Transform - Get Dtaa - From CSV/Text).

hi Ali,

thanks. Hmm but i will have to loop through each csv table and use them in workbook. It is possible with PQ to loop through folder?
plus i have formulas in table...like here:

Screenshot_29.png

hmm.
Jacek

Originally Posted by jaryszek

hi Ali,

thanks. Hmm but i will have to loop through each csv table and use them in workbook. It is possible with PQ to loop through folder?
plus i have formulas in table...like here:

Yes, you can import from a folder instead of an individual file, then merge the imported tables.

What you do is create the query result, then add your formulae to that table, which can then be refreshed moving forward.

Looks like Marc has given you a VBA option above, however.

Jaryszek is learning how to programme, so will probably want to go your way. Please remember, Marc, that you, too, were once a learner. There are not just programmers and non-programmers. You don't seem particularly tolerant of people wanting to learn VBA.

Well, you don't appear to be, and there is absolutely no rule against theoretical threads. You have made some very pointed comments in some of those threads, which were not needed (indeed, one of your comments was reported by another member only yesterday). Maybe you don't realise how you are coming across, and I realise that English isn't your first language, so that may be part of the problem.

Another ways requesting a 'single codeline' to open a delimited text file are the methods Workbooks.Open & Worbooks.OpenText
at very beginner level …

Marc L please provide example.
How this simple codeline looks like and how to create listobject from this or maybe it will be not necessary?

Ali thank you very much,

Jaryszek is learning how to programme, so will probably want to go your way. Please remember, Marc, that you, too, were once a learner.

hmm i like your solution. How can i loop through folder and take tables?
I see few issues. What if i will have formula in one column? How PQ will skip this ? It is possible?
formula:

=INDEX(INDIRECT("t_DataCentersSource[LocationID]");MATCH([@RegionName];INDIRECT("t_DataCentersSource[DataCenter]");0))

is in column C.
I added worksheet with Table2 into Main.xlsx.
And in TableList i added tables list which i want to be consumed by PowerQuery - so only these ones on the list:

Screenshot_30.png

and second issue: it is possible to block user before refreshing power query?
I will refresh tables by my own and do not want user to do it by his own.

Maybe somehow after reading data unlink table? and after that link tables while loading ?

Haluk - your code works perfect - thank you.
But it will not work if i will have formula in main workbook table. How your code can avoid this?
Maybe changing SQL statement ?
Or looping through each table's column?
Goal is to skip importing the column where already formula is. So in this case i know that this is column C.

Best,
Jacek

Originally Posted by jaryszek

.....
...
..

Haluk - your code works perfect - thank you.
But it will not work if i will have formula in main workbook table. How your code can avoid this?
Maybe changing SQL statement ?
....
..

Give this a try;

Sub Test2()
    'Haluk - 01/07/2019
    'sa4truss@gmail.com
    
    Dim strFile As Variant
    Dim myFile As String
    Dim myFolder As String
    Dim strSQL As String
    '
    Dim objConn As Object, RS As Object, FSO As Object
    Dim i As Integer, j As Integer
    
    Const adOpenForwardOnly = 1
    Const adLockReadOnly = 1
    Const adCmdText = 1
    
    strFile = Application.GetOpenFilename("CSV files,*.csv")
    If strFile = False Then Exit Sub
    
    Set objConn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    myFile = FSO.getFileName(strFile)
    myFolder = FSO.GetFile(strFile).ParentFolder.Path & Application.PathSeparator
    
    objConn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
                 "Dbq=" & myFolder & ";Extensions=asc,csv,tab,txt;"
    
    strSQL = "Select [ResourceID],[RegionName],[Role] from [" & myFile & "]"
    RS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
    
    For i = 0 To RS.RecordCount - 1
        Cells(i + 2, 1) = RS(0)
        Cells(i + 2, 2) = RS(1)
        Cells(i + 2, 4) = RS(2)
    Next
    
    RS.Close
    Set RS = Nothing
    objConn.Close
    Set objConn = Nothing
End Sub

.

Thank you Olly!

The solution is very nice.
But i can not protect view because after unlinking PQ users should have possibility to input new data into table. So in Excel i should have mechanism for creating connection to CSVs based on tables names from workbook and after loading - unlink them.

And the read data is different for each table - each table can be different so it would be not that easy to read.
And additionally i have to skip also columns with formulas. So only update existing tables, not replace.

I think that i will try with Haluk's code (and still waiting for Marc L'solution who is talking a lot but not providing any specific examples).

But problem with Haluk's code is that fields are manually provided. I will know only that for example for column numner 3 (RegionID) there is formula so i have to skip this column while loading.

I thought to :
0. Code for opening recorset is very nice.
1. Assign array from opened recordset.
i am trying with :

 RS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly
    
    RS.movefirst
    arrVar = RS.getrows

but i am getting error "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

2. Use application.index with dynamic array to specify which column should i output.

So if i know that i want to skip column = 3 (RegionID) i could use something like here:

[a2].Resize(dicTest.Count, 4) = Application.Index(dicTest.items, Evaluate("row(1:" & dicTest.Count & ")"), Array(4, 1, 3, 2))

But maybe i will have to loop through table and if i have number = 3 just skip.

Any help much appreciated,
Jacek

Originally Posted by jaryszek

.....
...

......I will know only that for example for column numner 3 (RegionID) there is formula so i have to skip this column while loading.
....
...
..

We can check if the cell has an formula and if not exists, then it can be feeded from the recordset as follows;

Sub Test3()
    'Haluk - 02/07/2019
    'sa4truss@gmail.com
    
    Dim strFile As Variant
    Dim myFile As String
    Dim myFolder As String
    Dim strSQL As String
    '
    Dim objConn As Object, RS As Object, FSO As Object
    Dim i As Integer, j As Integer
    
    Const adOpenKeyset = 1
    Const adLockReadOnly = 1
    Const adCmdText = 1
    
    strFile = Application.GetOpenFilename("CSV files,*.csv")
    If strFile = False Then Exit Sub
    
    Set objConn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    myFile = FSO.getFileName(strFile)
    myFolder = FSO.GetFile(strFile).ParentFolder.Path & Application.PathSeparator
    
    objConn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
                 "Dbq=" & myFolder & ";Extensions=asc,csv,tab,txt;"
    
    strSQL = "Select [ResourceID],[RegionName],[RegionID],[Role] from [" & myFile & "]"
    RS.Open strSQL, objConn, adOpenKeyset, adLockReadOnly, adCmdText
    
    For i = 0 To RS.RecordCount - 1
        If Not Cells(i + 2, 1).HasFormula Then Cells(i + 2, 1) = RS(0)
        If Not Cells(i + 2, 2).HasFormula Then Cells(i + 2, 2) = RS(1)
        If Not Cells(i + 2, 3).HasFormula Then Cells(i + 2, 3) = RS(2)
        If Not Cells(i + 2, 4).HasFormula Then Cells(i + 2, 4) = RS(3)
    Next
    
    RS.Close
    Set RS = Nothing
    Set FSO = Nothing
    objConn.Close
    Set objConn = Nothing
End Sub

.

I will call on Olly to chip in here - he is the PQ guru.

and second issue: it is possible to block user before refreshing power query?

Not sure - the refresh option is on the Data ribbon.

This will combine all CSV files in a folder:

let
    Source = Folder.Files("C:\yourfolder"),
    #"Filtered CSV" = Table.SelectRows(Source, each ([Extension] = ".csv")),
    fnGetCSV = (CSVfile) =>
    let
        Source = Csv.Document(CSVfile,[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
    in
        #"Promoted Headers",
    #"Get Data" = Table.AddColumn(#"Filtered CSV", "CSVdata", each fnGetCSV([Content])),
    #"Combine Data" = Table.Combine(#"Get Data"[CSVdata])
in
    #"Combine Data"

To prevent a query being refreshed, simply protect the worksheet on which the query is materialised.

@Olly - thanks for chipping in.

Try providing more meaningful sample files, and expected output. I'm not quite clear on what you're trying to achieve...

I don't understand. Why are you adding formulae to the main workbook when the .csv files already contain the raw data for those columns?

Sorry - makes no sense to me at all. You need to provide data that properly reflects the real data you are working with.

I am not asking for personal data!

CLARITY is what we need - you have now clarified this. Thanks for that.

I don't understand why you are using the volatile INDIRECT function in one of your two formulae when it could simply be this:

=INDEX(t_DataCentersSource[LocationID],MATCH([@RegionName],t_DataCentersSource[DataCenter],0))

Marc - I am afraid that your posts are coming across as unhelpful and intolerant. As I have asked before, unless you have something informative or helpful to add, without making comments about other members' competences, then please don't post.

Originally Posted by Marc L

Is reading a competence ?

It is - but so is politeness.

I 2nd Ali's warning Marc. If you have nothing constructive (AND courteous) to add, then dont add anything. If your comments along these lines continue, your privileges may be revoked.

Thanks.

Enough, now, Marc. If this continues, I shall remove your posts. You have been warned.