• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Database SQL

Mohan76

New Member
Hi All

Looking for help in the below problem

The requirement is to validate a raw csv file after loading into excel sheet. I'm planning to use the approach of excel as database as there are rules based on individual record based on field values (e.g. if field 1 is blank then field 4 must be blank etc) and based on index field some validation to be performed at file level.

* Query 1 - SQL queries used when working 'Excel as Database' is same as SQL Server SQL ?
* Query 2 - If validation fails for any fields, i want to highlight them in different color ? Is this possible when we using 'Excel as Database', by update recordset ?

thanks in advance
 
Hi Mohan,

1)The SQL statements for VBA remains same as in SQL Server
2)Formatting in the Data base with 'update recordset' will not be possible

I am using excel as database and frontend with SQL approach (Query, Update, Insert)
If you could explain your requirement much more I would be able to help!
 
Hi Satish

Many thanks for your reply.

As in the below example, there are two types of record, 1. contract & 2. account associated with each contract.

File Level
Rule 1 : Validation Rule is account start date should be on or after contract start date of the respective contract. If the loaded raw file has any values not matching this rule, i want to highlight the respective account start date in different color.

Record Level
Rule 2 : If contract status is CONFIRMED then CONTRACT END DATE should be completed

e.g.

CONTRACT ID CONTRACT START DATE CONTRACT END DATE CONTRACT STATUS
1001 01/01/2014 31/12/2014 CONFIRMED
1002 02/02/2014 DRAFT

CONTRACT ID ACCOUNT NO START DATE END DATE
1001 9001 10/01/2014 31/12/2014
1001 9002 10/01/2014 31/12/2014
1002 9004 10/02/2014 01/02/2015

regards
Mohan
 
Hi Sathish

Yes, its fresh. Just want to understand which is the right way of doing and struggling find the right approach to proceed with.

regards
Mohan
 
Hi Mohan,

You should be having two files now
One is your database and another one is your upload file
Correct me if I am wrong

In that case provide me the path, file name, sheet name and range to be used

Or Else

Upload sample files
 
Thanks for your quick response

Due to data protection, can't upload the actual file. I herewith sharing a model file, all records will be in single file. I'll be loading this in to sheet and then refresing that as database to apply the validations.

where field description as below

CONTRACT : RECORD_TYPE,CONTRACT_ID,START_DATE,END_DATE,CONTRACT_STATUS
ACCOUNT : RECORD_TYPE,CONTRACT_ID,ACCOUNT_ID,START_DATE,END_DATE

SAMPLE FILE
CONTRACT,10001,01012014,31122014,CONFIRMED
ACCOUNT,10001,90001,10012014,31122014
ACCOUNT,10001,90002,10012014,31122014
CONTRACT,10002,10022014,,DRAFT
ACCOUNT,10002,90005,15022014,
ACCOUNT,10002,90006,15022014,
 
Hi Mohan,

I don't think that SQL coding would be required to achieve this.

It can be done with some formulas and conditional formatting

Also would like to know if the date's will always be in non date format
 
Hi Sathish

yes, the date will be in this format.

Mainly i thought of going for coding is, that this will keep growing and we have to do it for the new data loaded (appending in the end) . Also the rule list will keep increasing.

If there is way to make it simple, i would be happy to learn the technique.

regards
Mohan
 
Hi Mohan,

I have created a macro for your 1st rule

Please check and let me know if it satisfies your requirement

Note: The contract start date of contract id 10001 has only 7 digit. While others are 8 digit

Code:
Sub Macro3()
  
  
  Sheets("CONTRACT").Select
  
'Converting all date columns to actual date format
  
  Columns("C:C").Select
  Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
  Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
  :=Array(1, 4), TrailingMinusNumbers:=True
  
  Columns("D:D").Select
  Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
  Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
  :=Array(1, 4), TrailingMinusNumbers:=True
  
  
  Sheets("ACCOUNT").Select
  
  Columns("D:D").Select
  Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
  Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
  :=Array(1, 4), TrailingMinusNumbers:=True
  
  Columns("E:E").Select
  Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
  Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
  :=Array(1, 4), TrailingMinusNumbers:=True
  
  
'Defining the last active row
  
  lr = Range("B1048576").End(xlUp).Row
  
  
'creating a conditional formatting for Rule:1
  
  Range("D2:D" & lr).Select
  Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  "=$D2<VLOOKUP($B2,CONTRACT!$B:$C,2,0)"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Interior
  .PatternColorIndex = xlAutomatic
  .Color = 65535
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  
End Sub
 
Hi Sathish

It worked with correction in vlookup (reference to another sheet) ...thanks a lot. Learned a new (simple) way.

regards
Mohan
 
Back
Top